Extracting last name from full name

[bearcub]

I have to extract the last name and join it with elements to create a position title that is used in an application.

This is the formula:

TRIM(E10&"-"&F10&"-"&UPPER(RIGHT(A10,SEARCH(" ",A10))))&"-"&G10

The issue I am having with is in the Upper(Right) section that extracts the last name from the name.

The formula works okay until I have people a middle name, etc. The formula works well only if their are 2 works. The formula goes wonky when I have a 1st, middle & last name.

Is there a formula that will find the last space in the name and extract the last name? Note row 10, this person has 4 names instead of 2. And the result is including the 2nd, 3rd & 4th words of the name.

This is the source data and results (Column C)

2023 New Hires and Terminations.xlsb
	A	B	C
1	Month:	Oct 22
2	Employee	Start Date	Position
3	Veronika Westerlund	10/3/2022	EMEA-SALES- WESTERLUND-7F7R8MA6K
4	Andy Dedman	10/3/2022	EMEA-CS- DEDMAN-VO6YY6PJP
5	Roberta Polesi	10/3/2022	LATAM-CS- POLESI-RHS8ECV5E
6	Brandon Jackson	10/3/2022	NALA-ADR- JACKSON-PUHP7ZZIY
7	Jatinder Luthra	10/10/2022	NALA-SE- LUTHRA-G5REO3FF1
8	Hervé Tchaha	10/17/2022	EMEA-SALES- TCHAHA-KYKT6MJXH
9	Thore Rabe	10/17/2022	EMEA-SALES- RABE-LUNB98N9Z
10	Gener Javier Perez Torres	10/31/2022	EMEA-CS- JAVIER PEREZ TORRES-PSN485Q94
11	Darrick Webster	10/17/2022	NALA-SE- WEBSTER-GFHM0NH7G
Oct 22

Cell Formulas
Range	Formula
C11	=TRIM(E11&"-"&F11&"-"&UPPER(RIGHT(A11,SEARCH(" ",A11))))&"-"&G11
Press CTRL+SHIFT+ENTER to enter array formulas.

XD]C11[/XD]

 

[JvdV]

I see you have ms365, so try the below:

=TEXTAFTER(A2," ",-1)

 

[bearcub]

Yes, your formula works great as well. I get a little confused when I see the substitute function since I don't use it much. So, internally you're adding 100 spaces before Torres which in a way makes it dynamic in conjunction with the Trim function. Really cool, I like it! I always am interested in seeing different formulas for solving the same issue.

could you walk me through what the Find portion of the formula is doing?

RIGHT(A3,LEN(A3)-FIND("*",SUBSTITUTE(A3," ","*",LEN(A3)-LEN(SUBSTITUTE(A3," ",""))).

It looks like the find is returning the entire name minus the space?

 

[DanteAmor]

Thank you. Does Flash fill join different columns?

Flash fill is not a formula, you just start capturing the data in the right column, excel recognizes the pattern and performs it on all the data.
You can use this column and then concatenate this column with the other columns you already have.

 

[Dan_W]

Yes, your formula works great as well. I get a little confused when I see the substitute function since I don't use it much. So, internally you're adding 100 spaces before Torres which in a way makes it dynamic in conjunction with the Trim function. Really cool, I like it! I always am interested in seeing different formulas for solving the same issue.

could you walk me through what the Find portion of the formula is doing?

RIGHT(A3,LEN(A3)-FIND("*",SUBSTITUTE(A3," ","*",LEN(A3)-LEN(SUBSTITUTE(A3," ",""))).

It looks like the find is returning the entire name minus the space?

I could walk you through the formula, but I'm going to suggest you check out the explanation helpfully provided by Dave Bruns instead: Excel formula: Get last name from name | Exceljet

I would add that JvdV's suggestion above is quite elegant - it's only currently available on MS365 (as far as I know), so if you send a workbook with a formula containing TEXTAFTER to someone without MS365, your day will almost certainly end with tears and screaming and recriminations and a series of blood feuds getting started ... I speak from experience... LOL

That said, honestly, I'd be minded to remake the TEXTAFTER function in VBA, but VBA is usually an issue for some people... Just a thought.

 

[bearcub]

Thank you. I"ll check out Dave's site, I'm familiar with it. I just read the explanation and I understand what is happening.

In regards to Dante's flash fill explanation, it does look like I would have to have another formula in another column which is workable as well. Thank you Dante.

 

[Automatrix]

Power Query solution:

 

[bearcub]

Thank you for the Power Query solution. I didn't think about that.