USE PART OF TEXT FROM ONE CELL IN ANOTHER

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 ?
 
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("NAME
",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
 
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.
 
Back
Top