Extract First and Last Names

  • Thread starter Thread starter Vincent
  • Start date Start date
V

Vincent

Hi:
I have user name field with last name, coma then first
name, like "Smith, John".
Which function I can use to extract the last name and
first name separately.
Thanks in advance!
Vincent
 
You asked for a function -- the most fun -- so:

A1 = Smith, John
B1 = Last name: LEFT(A1,FIND(",",A1)-1)
C1 = First name: RIGHT(A1,(LEN(A1)-2)-LEN(B1))

This assumes all names are laid out as you wrote.
 
Hi Dave
You could use Data>Text_to_Columns and select the comma as your deliminator. This will do the entire column in one shot

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- Dave R. wrote: ----

You asked for a function -- the most fun -- so

A1 = Smith, Joh
B1 = Last name: LEFT(A1,FIND(",",A1)-1
C1 = First name: RIGHT(A1,(LEN(A1)-2)-LEN(B1)

This assumes all names are laid out as you wrote
 
Hi Mark,
Yah I know about that, but for some reason I love to pull things apart with
the text functions. Vince asked for a function, so maybe he is dealing with
data that is updated/imported and he doesn't want to continually mess with
his columns.


Mark Graesser said:
Hi Dave,
You could use Data>Text_to_Columns and select the comma as your
Click to expand...
deliminator. This will do the entire column in one shot.
 
Hi Dave
The more options the better, I always say

I meant to addres my first post to Vincent, just got lost in the thread

Regards
Mark Graesse
(e-mail address removed)
Boston M

----- Dave R. wrote: ----

Hi Mark
Yah I know about that, but for some reason I love to pull things apart wit
the text functions. Vince asked for a function, so maybe he is dealing wit
data that is updated/imported and he doesn't want to continually mess wit
his columns


Mark Graesser said:
Hi Dave
You could use Data>Text_to_Columns and select the comma as you
Click to expand...
deliminator. This will do the entire column in one shot
 
Well you are welcome Pam. If you want more flexible approaches, go to google
and search through the groups for extracting first and last names. Ive seen
many times, and know there have been many more, when this topic is addressed
with other formulas (and without formulas).

The formulas for this post aren't the most flexible if your data isn't laid
out exactly as Vincent's.
 
Hi Dave:
You're right about the format being the same.
This is where I'm at now:
A1: Smith, John Q (original data)
B1: Smith (using your help)
C1: John Q (using your help, but I just want first name)
D1: And of course, this is where I'd love the middle
initial to be.

Any suggestions?

I did go to Google/Groups and look a little, but must
confess I did not have much luck. I am not real "up to
speed" on knowing what group to go to. And when I searched
I got too much extraneous info.
 
Hi there.
A1= Smith, Bill A

B1 Last name u already know,

C1 first name
=MID(A1, FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)

D1 middle initial
=RIGHT(A1,1)

also,

http://www.cpearson.com/excel/FirstLast.htm

is a good place to look for this. I haven't played with them, so don't know
how they'd deal with commas. If they don't work with commas, you can always
remove the commas from your text with Search + Replace "," with " ".
 
Back
Top