choose a particular word in a string

T

Tim

I have a list of names with both names in one cell. How do i choose the first
name and put into the next column? and then repeat that for the second name?
 
B

Bob Phillips

=LEFT($A1,IF(ISNUMBER(FIND(" ",$A1)-1),FIND(" ",$A1)-1,LEN($A1)))

=LEFT(SUBSTITUTE($A1,B1&" ",""),IF(ISNUMBER(FIND(" ",SUBSTITUTE($A1,B1&"
",""))),FIND(" ",SUBSTITUTE($A1,B1&" ",""))-1,LEN(SUBSTITUTE($A1,B1&"
",""))))

=LEFT(SUBSTITUTE($A1,B1&" "&C1&" ",""),IF(ISNUMBER(FIND("
",SUBSTITUTE($A1,B1&" "&C1&" ",""))),FIND(" ",SUBSTITUTE($A1,B1&" "&C1&"
",""))-1,LEN(SUBSTITUTE($A1,B1&" "&C1&" ",""))))

and so on

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

Tim said:
I have a list of names with both names in one cell. How do i choose the first
name and put into the next column? and then repeat that for the second
Click to expand...
name?
 
C

CLR

"Two basic techniques are used, Data > TextToColumns > follow the menu", or
"the use of the LEFT and MID functions". Both are dependent on exactly how
your names are in the cells, whether FirstName-space-LastName, or
LastName-,-space-FirstName, etc etc and whether any middle initials, periods,
or titles etc are used.

Vaya con Dios,
Chuck, CABGx3
 
P

Pete_UK

If the names are separated by a single space, then you can highlight
the column and use Data | Text-to-columns, specifying Delimited data
with space as the delimiter. You can specify where the data is to be
written to, if you want to keep the original column of names.

Hope this helps.

Pete
 
Top