As a seperate formulae I have been able to seperate the first and the last
words but I am having trouble identifying the count of the letters in the
middle word, I don't seem to be able to get to grips with the mid function.
I'm sure you will be able to put it all into one formulae at least I hope so,
please help.
--
Thanks Bob


"Bob" wrote:

I am still playing with this and have found a partial solution. The '*' I put
in caused a problem, find doesn't allow wild cards! without it the formulae
returns the names I want or almost, what it does do is give me

NAME fred bloggs
NAME freda bloggs
etc
I have only nine lines because that is the total number of NAME's in the
list, I'm sure I can extend this to include all the list. All I need to do
now is be able to seperate the three words into
drop the word NAME and put the
second word into F1 to F(number of names)
third word into G1 to G(number of names). can you help please?
--
Thanks Bob


"Bob" wrote:

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