to daddylonglegs, thanks for your reply I have been trying various ways of
implementing it , it shows up on the discussion site in four rows, I have
added (or replaced the space after the last word (NAME ) with) at the end of
row two with the * symbol. This lets excel allow it as a formulae. I have
placed it in C1 and confirmed it with CTRL-SHIFT-ENTER ( I think, is that
press all three instead of just ENTER? -does this do something different to
just enter?) and all I get is #NUM. I have copied it down the column but
notice the cell references don't change. I only get a #NUM in the first nine
cells ,even though I copied it further and there is at least one true cell in
column A (row 4). I failed to find the answer, do you see one? My list is in
A1 to A230. with a "NAME **** /****/" about every 10 but it does differ
slightly.
Thanks Bob


"daddylonglegs" wrote:

If your data is in A1:A100 then in C1 try the formula

=IF(COUNTIF(A$1:A$100,"NAME*")ROW()-ROW(C$1),INDEX(A$1:A$100,SMALL(IF(ISNUMBER(FIND("N AME
",A$1:A$100)),ROW(A$1:A$100)-ROW(C$1)+1,""),ROW()-ROW(C$1)+1)),"")

confirmed with CTRL+SHIFT+ENTER and copied down the column

"Bob" wrote:

I have the following in one cell NAME fred /bloggs/ then down to cells
with other info in them before another cell with NAME freda /bloggs/ etc
I have discovered how to check for the existance of "NAME" in a cell but how
can I copy the "fred" and "bloggs" to other cells when the "NAME" exists?
Is it also possible to check the next cell down if "NAME" doesn't appear in
a cell and carry on to the next other cell down so that I get
fred bloggs
freda bloggs
etc ?
--
Thanks Bob