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