Extract prepositions next to last names only if the name is the last word in a string

[RonOliver]

Hi, everyone. Need some help with a stumper.

I'm trying to create a tool that splits the name and last name in a cell into three cells (one cell for the first name and the other two for both last names, as we have two last names in Spanish). The rest of the formula is already put together correctly. Here's the problem. It's gonna be a wild ride, so bear with me, please.

The main problem here is some last names in Spanish have prepositions next to them (such as "de la Torre" or "del Pino"). I tried to create a formula that either takes the last name OR it takes all the text at the right of the cell starting from the preposition. This is the formula that I wrote:

=IFERROR(RIGHT(C19;LEN(C19)-FIND(" de la ";C19));TEXTAFTER(C19;" ";-1))

This formula works well if the full name is "Carlos Fernández de la Torre" (it returns "de la Torre"), but not if the name is "Jorge de la Torre Fernández" (it returns "de la Torre Fernández"). I need a formula that is able to return the last name (including "de la") ONLY if the word to the right of "de la" is the last word in the string of text. The desired result if C19 was "Jorge de la Torre Fernández" is "Fernández". However, if C19 was "Carlos Fernández de la Torre", the desired result would in turn be "de la Torre". I know the problem is a bit convoluted, but I appreciate all the help I get.

 

[rlv01]

One way.

Test.xlsm
	A	B
1	Name	Last Name
2	Carlos Fernández de la Torre	de la Torre
3	Jorge de la Torre Fernández	Fernández
4	Sylvia Gutierrez Velarde	Velarde
Sheet13

Cell Formulas
Range	Formula
B2:B4	B2	=LET(Name,A2,TmpStr, TRIM(RIGHT(SUBSTITUTE(Name," ",REPT(" ",100)),50)),IF(MID(Name,LEN(Name)-LEN(TmpStr)-5,5)="de la","de la " & TmpStr,TmpStr))

 

[RonOliver]

Thanks so very much. I have never used LET, so I don't know what "Name" and "TmpStr" are placeholders of. I tried to paste it straight to my file replacing the cells, but I can't get it to work. Could you help me some more?

 

[rlv01]

First try pasting my example to a new worksheet.

Then you must explain any problem in detail. Phrases like "it does not work" are too vague.

LET function - Microsoft Support

support.microsoft.com

The free XL2BB tool (link below) is available to post your data in a way that makes it accessible to others.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[RonOliver]

I'm sorry. I used Excel in a different language on a computer I don't own. Thanks so much!!