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]

 

[DanteAmor]

Using Flash fill in Excel.
Type the last name in the first cell, type the last name in the second cell. Flash fill get last name for all cells. Make the correction to the Gener name. Done, Flash fill considers those patterns to fill all the cells.

 

[bearcub]

Thank you. Does Flash fill join different columns? I am joining several cells and using "-" to create the position name?

I think I did find a formula that works. I'm wondering if their is a simplier formula that I have for the right function:

=TRIM(E3&"-"&F3&"-"&UPPER(RIGHT(A3,LEN(A3)-FIND("*",SUBSTITUTE(A3," ","*",LEN(A3)-LEN(SUBSTITUTE(A3," ","")))))&"-"&G3))

2023 New Hires and Terminations.xlsb
	A	B	C	D	E	F	G
1	Month:	Oct 22
2	Employee	Start Date	Position	Dept	Region	Function	EMP ID
3	Veronika Westerlund	10/3/2022	EMEA-SALES-WESTERLUND-7F7R8MA6K	430000	EMEA	SALES	7F7R8MA6K
Oct 22

 

[DanteAmor]

Try :

varios 26oct2022.xlsm
	A	B	C	D	E	F	G
1	Month:	oct-22
2	Employee	Start Date	Position	Dept	Region	Function	EMP ID
3	Veronika Westerlund		EMEA-SALES-Westerlund-7F7R8MA6K	43000	EMEA	SALES	7F7R8MA6K
4	Andy Dedman		EMEA-SALES-Dedman-7F7R8MA6K	43001	EMEA	SALES	7F7R8MA6K
5	Roberta Polesi		EMEA-SALES-Polesi-7F7R8MA6K	43002	EMEA	SALES	7F7R8MA6K
6	Gener Javier Perez Torres		EMEA-SALES-Javier Perez Torres-7F7R8MA6K	43003	EMEA	SALES	7F7R8MA6K
7	Darrick Webster		EMEA-SALES-Webster-7F7R8MA6K	43004	EMEA	SALES	7F7R8MA6K
8	Dante Amor Mendiola		EMEA-SALES-Amor Mendiola-7F7R8MA6K	43005	EMEA	SALES	7F7R8MA6K
9	Brandon Jackson		EMEA-SALES-Jackson-7F7R8MA6K	43006	EMEA	SALES	7F7R8MA6K
10	Jatinder Luthra		EMEA-SALES-Luthra-7F7R8MA6K	43007	EMEA	SALES	7F7R8MA6K
11	Hervé Tchaha		EMEA-SALES-Tchaha-7F7R8MA6K	43008	EMEA	SALES	7F7R8MA6K
12	Thore Rabe		EMEA-SALES-Rabe-7F7R8MA6K	43009	EMEA	SALES	7F7R8MA6K
Hoja7

Cell Formulas
Range	Formula
C3:C12	C3	=E3&"-"&F3&"-"&TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",99),1),99,99))&"-"&G3

 

[bebo021999]

Book1
	A	B	C	D	E	F	G
2	Employee	Start Date	Position	Dept	Region	Function	EMP ID
3	Veronika Westerlund		EMEA-SALES-Westerlund-7F7R8MA6K	43000	EMEA	SALES	7F7R8MA6K
4	Andy Dedman		EMEA-SALES-Dedman-7F7R8MA6K	43001	EMEA	SALES	7F7R8MA6K
5	Roberta Polesi		EMEA-SALES-Polesi-7F7R8MA6K	43002	EMEA	SALES	7F7R8MA6K
6	Gener Javier Perez Torres		EMEA-SALES-Torres-7F7R8MA6K	43003	EMEA	SALES	7F7R8MA6K
7	Darrick Webster		EMEA-SALES-Webster-7F7R8MA6K	43004	EMEA	SALES	7F7R8MA6K
8	Dante Amor Mendiola		EMEA-SALES-Mendiola-7F7R8MA6K	43005	EMEA	SALES	7F7R8MA6K
9	Brandon Jackson		EMEA-SALES-Jackson-7F7R8MA6K	43006	EMEA	SALES	7F7R8MA6K
10	Jatinder Luthra		EMEA-SALES-Luthra-7F7R8MA6K	43007	EMEA	SALES	7F7R8MA6K
11	Hervé Tchaha		EMEA-SALES-Tchaha-7F7R8MA6K	43008	EMEA	SALES	7F7R8MA6K
12	Thore Rabe		EMEA-SALES-Rabe-7F7R8MA6K	43009	EMEA	SALES	7F7R8MA6K
Sheet1

Cell Formulas
Range	Formula
C3:C12	C3	=E3&"-"&F3&"-"&TRIM(RIGHT(SUBSTITUTE(A3," ",REPT(" ",100)),100))&"-"&G3

 

[DanteAmor]

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.

In the case of 2 words you want the second.
In the case of 3 words, which ones do you want?
In the case of 4 words, which ones do you want?
The names in Spanish include more than 4 words, they can have up to 7 or more words, in those cases how many words do you want?

 

[bearcub]

I always only the last name. We normally have reps that have a first and last name but, as you can by the example, we have a new hire that has 4 names. I want the formula to strip everything away except for Torres.

 

[bearcub]

This formula works well too: TRIM(RIGHT(SUBSTITUTE(A3," ",REPT(" ",100)),100)). Is this formula increasing the number of spaces to the left of Torres so that only Torres is returned?

 

[bebo021999]

This formula works well too: TRIM(RIGHT(SUBSTITUTE(A3," ",REPT(" ",100)),100)). Is this formula increasing the number of spaces to the left of Torres so that only Torres is returned?

Did you mention to my formula in #5?
Last space is replaced with 100 spaces, to make sure last 100 letters include space and "Torres", like: " Torres"
Then using TRIM to get "Torres"

 

[Dan_W]

THis just take DanteAmor's excellent work, and slightly adapts it. You can see the new "Last Name" column on the right, but I've added the new formula into Column C to demonstrate how it works.

Book1
	A	B	C	D	E	F	G	H
1	Employee	Start Date	Position	Dept	Region	Function	EMP ID	LastName
2	Veronika Westerlund		EMEA-SALES-Westerlund-7F7R8MA6K	43000	EMEA	SALES	7F7R8MA6K	Westerlund
3	Andy Dedman		EMEA-SALES-Dedman-7F7R8MA6K	43001	EMEA	SALES	7F7R8MA6K	Dedman
4	Roberta Polesi		EMEA-SALES-Polesi-7F7R8MA6K	43002	EMEA	SALES	7F7R8MA6K	Polesi
5	Gener Javier Perez Torres		EMEA-SALES-Torres-7F7R8MA6K	43003	EMEA	SALES	7F7R8MA6K	Torres
6	Darrick Webster		EMEA-SALES-Webster-7F7R8MA6K	43004	EMEA	SALES	7F7R8MA6K	Webster
7	Dante Amor Mendiola		EMEA-SALES-Mendiola-7F7R8MA6K	43005	EMEA	SALES	7F7R8MA6K	Mendiola
8	Brandon Jackson		EMEA-SALES-Jackson-7F7R8MA6K	43006	EMEA	SALES	7F7R8MA6K	Jackson
9	Jatinder Luthra		EMEA-SALES-Luthra-7F7R8MA6K	43007	EMEA	SALES	7F7R8MA6K	Luthra
10	Hervé Tchaha		EMEA-SALES-Tchaha-7F7R8MA6K	43008	EMEA	SALES	7F7R8MA6K	Tchaha
11	Thore Rabe		EMEA-SALES-Rabe-7F7R8MA6K	43009	EMEA	SALES	7F7R8MA6K	Rabe
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=E2&"-"&F2&"-"&RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))&"-"&G2
H2:H11	H2	=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))