Extracting last name from a string

[rmichra]

Hi,

I have names entered into a cell per the following:

John Q Smith

Bob Allen Jones

William B Howard


How do I extract in another cell the last name using a function?

[AlKey]

Assuming the last name is always the last word.
Enter in B1 and copy down

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),50))

v	A	B
1	John Q Smith	Smith
2	Bob Allen Jones	Jones
3	William B Howard	Howard

[rmichra]

Thank you! That worked just fine.

I also found out one of the files has names listed like this:

Smith, Brian L
Jones, Evan R
Gray, David S "Buddy"

How do I extract the first name in to a separate cell using a function/formula?

(Using the "Mid" function I can get to the correct starting position but don't know how to get it to compute the right length)

[rmichra]

I need to extract the first name

[sandy666]

Just for fun
Done with PowerQuery:

[sandy666]

First name from:

John Q Smith
Bob Allen Jones
William B Howard
Smith, Brian L
Jones, Evan R
Gray, David S "Buddy"

done with PowerQuery

[rmichra]

Can this be done using a combination of functions?

[sandy666]

Probably yes but I prefer make life easier not harder

maybe someone else will give you formula solution

have a nice day

[FDibbins]

if there will ALWAYS be a comma for that format and NOT in the other format, then try this...
=IFERROR(LEFT(B14,SEARCH(",",B14)-1),TRIM(RIGHT(SUBSTITUTE(B14," ",REPT(" ",50)),50)))

[rmichra]

The name will always have a comma.

I used the formula and it extracted the last name. I need the name following the comma.

[sandy666]

Hi Ford,
What about list from post#6?

[FDibbins]

If there is always a comma when the last name appears 1st - and NO comma when the last name is last, that will handle them both

	B	C
14	Smith, Brian L	Smith
15	Jones, Evan R	Jones
16	Gray, David S "Buddy"	Gray
17	John Q Smith	Smith
18	Bob Allen Jones	Jones
19	William B Howard	Howard

[sandy666]

I was thinking about result like this:

John
Bob
William
Brian
Evan
David

First name

	D	E
1	RAW	Extracted
2	John Q Smith	John
3	Bob Allen Jones	Bob
4	William B Howard	William
5	Smith, Brian L	Brian
6	Jones, Evan R	Evan
7	Gray, David S "Buddy"	David

I'm curious. I need to write monster formula to extract middle part (first name) from text with comma than extract first part from text without comma all with IF or you know something shorter

[FDibbins]

OK, I missed that you wanted the 1st name, if there is a comma, I will work something out

[rmichra]

Thank you!

Here are 2 examples how the names can be listed:

Smith, Billy R
Smith, Billy A "Ben"

I need to extract the first name.

[FDibbins]

Try this...
=IF(ISERROR(FIND(",",B14)),TRIM(RIGHT(SUBSTITUTE(B14," ",REPT(" ",50)),50)),LEFT(MID(B14,LEN(LEFT(B14,SEARCH(" ",B14,1)-1))+2,99),FIND(" ",MID(B14,LEN(LEFT(B14,SEARCH(" ",B14,1)-1))+2,99))-1))

	B	C
14	Smith, Brian L	Brian
15	Jones, Evan R	Evan
16	Gray, David S "Buddy"	David
17	John Q Smith	Smith
18	Bob Allen Jones	Jones
19	William B Howard	Howard

[rmichra]

Thank You.

[sandy666]

or maybe this one:

	A	B	C
1	RAW	Result	Formula
2	John Q Smith	John	=IFERROR(MID(A2,SEARCH(",",A2)+2,SEARCH(" ",MID(A2,SEARCH(",",A2)+2,99))-1),LEFT(A2,SEARCH(" ",A2)-1))
3	Bob Allen Jones	Bob
4	William B Howard	William
5	Smith, Brian L	Brian
6	Jones, Evan R	Evan
7	Gray, David S "Buddy"	David

[AlKey]

Try this one

=TRIM(MID(SUBSTITUTE(IF(ISERR(FIND(",",A2))," "&A2,A2)," ",REPT(" ",50)),50,50))

v	A	B
1	Name
2	John Q Smith	John
3	Bob Allen Jones	Bob
4	William B Howard	William
5	Smith, Brian L	Brian
6	Jones, Evan R	Evan
7	Gray, David S "Buddy"	David
8	Smith, Billy R	Billy
9	Smith, Billy A "Ben"	Billy

[rmichra]

That works perfect. Thank you. :-)

[rmichra]

Hello again,

I was using your formula and it was working great. However, I got an erroneous result when I encountered a name like:
Smith Jr., Billy A "Ben"

The formula returns the value "Jr.".

I tried messing with the formula to get it to work but no avail.

Your help would be greatly appreciated.

[FDibbins]

...
I was using your formula and it was working great....

Who's formula did you use?

[rmichra]

=trim(mid(substitute(if(iserr(find(",",a2))," "&a2,a2)," ",rept(" ",50)),50,50))

[jtakw]

Hi,

If there's Always a Space after the comma use B2 formula, otherwise use C2 formula:

Excel 2016 (Windows) 64 bit

	A	B	C
1	Name
2	John Q Smith	John	John
3	Bob Allen Jones	Bob	Bob
4	William B Howard	William	William
5	Smith, Brian L	Brian	Brian
6	Jones, Evan R	Evan	Evan
7	Gray, David S "Buddy"	David	David
8	Smith, Billy R	Billy	Billy
9	Smith, Billy A "Ben"	Billy	Billy
10	Smith Jr., Billy A "Ben"	Billy	Billy
11	Smith Jr.,Billy A "Ben"	illy	Billy

Sheet: Sheet44

Excel 2016 (Windows) 64 bit

	B	C
2	=TRIM(LEFT(SUBSTITUTE(MID(A2,IFERROR(FIND(",",A2)+2,1),255)," ",REPT(" ",100)),100))	=TRIM(LEFT(SUBSTITUTE(TRIM(MID(A2,IFERROR(FIND(",",A2)+1,1),255))," ",REPT(" ",100)),100))

Sheet: Sheet44

[jtakw]

Have you tried the formulas in my Post #22?