Extract a First Name from a String

[John]

Hi

How can I extract a First Name fro a String within a cell

My format of the cell is that the First name is always the first characters
within a cell, then there is a space then the surname starts inthe same
cell. These 'Full Name' are in Cell B. I want to remove the 'First Names'
from Cell B and place the within Cell C. How can I do that?

Thanks

 

[CLR]

Hi John..........

You can select the whole column with your names in it, then use Data > Text
to columns > Delimited . and use "space" as a delimiter..............this
will separate the names into columns for each............then just switch
the columns back and forth with Cut > Paste as you wish............

Vaya con Dios,
Chuck, CABGx3

 

[John]

Thanks for your reply, problem with space delimiters is that some names have
3 separate names eg. Hoo Chi Ming, where Hoo is the 'First Name' I want in
Column C with Chi Ming in Column B, suppose I could then use the & function
to combine

 

[Peo Sjoblom]

Hoo Chi Ming must be some Chinese relative to Ho Chi Minh? <g>
Secondly, Minh is the first name, Ho the last and Chi the middle.
Assuming that you only wanted to use that as an example and that you are
looking for a western type of name
with space as delimiters and assuming 3 names to parse

To get the last name

=MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,255)

or

=RIGHT(A2,LEN(A2)-SEARCH("^^",SUBSTITUTE(A2,"
","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

to get the first

=LEFT(A2,FIND(" ",A2)-1)

to get the middle you can simply substitute the other names with nothing
Assume the first is in D2, the last in F2 so in E2 use

=TRIM(SUBSTITUTE(SUBSTITUTE(A2,F2,""),D2,""))


copy all formulas and paste special as values, now some names (5 names etc
von and van and III and JR) you might need to handle manually or using
fairly
complicated solution, you could always do a Google search on Harlan Grove
and parsing names or something similar


--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Ron Rosenfeld]

Hi

How can I extract a First Name fro a String within a cell

My format of the cell is that the First name is always the first characters
within a cell, then there is a space then the surname starts inthe same
cell. These 'Full Name' are in Cell B. I want to remove the 'First Names'
from Cell B and place the within Cell C. How can I do that?

Thanks

If Fullname is in A1, then First Name:

=LEFT(A1,FIND(" ",A1)-1)

and the rest of the name:

=TRIM(MID(A1,FIND(" ",A1),255))


--ron