SUBSTITUTE function with a range of values to substitute

[RonOliver]

Hi, everyone,

I have been working on a formula that splits full names into names and surnames, but I have a problem. Let me tell you what it is:

Avaluació 2022-23 PROVES.xlsx
	C	D	E	F
4	Jorge Guzmán Pedraza	Jorge	Guzmán	Pedraza
5	David Tejero García	David	Tejero	García
6	Tamara Bas Moreno	Tamara	Bas	Moreno
7	Miguel Ureña Pérez	Miguel	Ureña	Pérez
8	Mónica Estadellas Vaca	Mónica	Estadellas	Vaca
9	Lena Viejo Alegre	Lena	Viejo	Alegre
10	Francesco Schettino	Francesco	Schettino
11	Marta Arenas Cabrerizo	Marta	Arenas	Cabrerizo
12	Aura López Alcázar	Aura	López	Alcázar
13	Javier Alarcón Aznar	Javier	Alarcón	Aznar
14	Nabil Hraira	Nabil	Hraira
15	Javier Fernández Parra	Javier	Fernández	Parra
16	Fátima Andrade Bayarri	Fátima	Andrade	Bayarri
17	Noemi Caravantes Fenollosa	Noemi	Caravantes	Fenollosa
18	Gabriela López-Moreno del Pino	Gabriela	López-Moreno	del Pino
19	Carlos de la Torre Ramírez	Carlos	de-la Torre	Ramírez
20	Juan Antonio García García	Juan Antonio	García	García
21	María de los Ángeles de Ojeda del Pino	María de-los Ángeles	de Ojeda	del Pino
Separadora

Cell Formulas
Range	Formula
D15:F21,D14:E14,D11:F13,D10:E10,D4:F9	D4	=LET(t,TRIM(C4),nm,IF(ISNUMBER(FIND(",",t)),MID(t&" "&t,FIND(",",t)+2,LEN(t)-1),t),arr,SUBSTITUTE(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(nm," y "," y#")," Y "," Y#")," i "," i#")," I "," I#")," dels "," dels#")," Dels "," Dels#")," de les "," de-les")," De les "," De-les")," els "," els#")," Els "," Els#")," les ","les#")," Les "," Les#")," De La "," De-La#")," De Las "," De-Las#")," De Los "," De-Los#")," Del "," Del#")," De "," De#")," El "," El#")," La "," La#")," Los "," Los#")," Las "," Las#")," de la "," de-la#")," de las "," de-las#")," de los "," de-los#")," de "," de#")," del "," del#")," el "," el#")," la "," la#")," los "," los#")," las "," las#")," "),"#"," "),last,TAKE(arr,,-2),first,TEXTJOIN(" ",1,DROP(arr,,-2)),IF(nm="","",IF(LEN(nm)-LEN(SUBSTITUTE(nm," ",""))+1=2,HSTACK(last),HSTACK(first,last))))
Dynamic array formulas.

The thing is some languages (like Spanish and German) have prepositions and articles that go with last names. The formula that I'm using correctly identifies the prepositions and takes them with their corresponding last name. However, as the list of prepositions to substitute increases in size, the formula gets clunkier and clunkier. I'd like to use a helper column where I can get all possible variations of prepositions and articles, so that the formula can do the same thing the current formula is doing without having to copy the full list of prepositions on every single cell (pretty muchthe way it works on the formula, but on a column, which would definitely shrink the formula substantially). I've tried using a range in the substitute function, but I couldn't get it to work. What can I do? Thanks for your help!

 

[Peter_SSs]

Try this

RonOliver_1.xlsm
	C				G	H	I	J	K
4	Jorge Guzmán Pedraza				Jorge	Guzmán	Pedraza		y
5	David Tejero García				David	Tejero	García		Y
6	Tamara Bas Moreno				Tamara	Bas	Moreno		i
7	Miguel Ureña Pérez				Miguel	Ureña	Pérez		I
8	Mónica Estadellas Vaca				Mónica	Estadellas	Vaca		dels
9	Lena Viejo Alegre				Lena	Viejo	Alegre		Dels
10	Francesco Schettino				Francesco	Schettino			de les
11	Marta Arenas Cabrerizo				Marta	Arenas	Cabrerizo		De les
12	Aura López Alcázar				Aura	López	Alcázar		els
13	Javier Alarcón Aznar				Javier	Alarcón	Aznar		Els
14	Nabil Hraira				Nabil	Hraira			les
15	Javier Fernández Parra				Javier	Fernández	Parra		Les
16	Fátima Andrade Bayarri				Fátima	Andrade	Bayarri		De La
17	Noemi Caravantes Fenollosa				Noemi	Caravantes	Fenollosa		De Las
18	Gabriela López-Moreno del Pino				Gabriela	López-Moreno	del Pino		De Los
19	Carlos de la Torre Ramírez				Carlos	de-la Torre	Ramírez		Del
20	Juan Antonio García García				Juan Antonio	García	García		De
21	María de los Ángeles de Ojeda del Pino				María de-los Ángeles	de Ojeda	del Pino		El
22									La
23									Los
24									Las
25									de la
26									de las
27									de los
28									de
29									del
30									el
31									la
32									los
33									las
Sheet1

Cell Formulas
Range	Formula
G15:I21,G14:H14,G11:I13,G10:H10,G4:I9	G4	=LET(t,TRIM(C4),nm,IF(ISNUMBER(FIND(",",t)),MID(t&" "&t,FIND(",",t)+2,LEN(t)-1),t),arr,SUBSTITUTE(TEXTSPLIT(REDUCE(nm,K$4:K$33,LAMBDA(s,r,SUBSTITUTE(s," "&r&" "," "&SUBSTITUTE(r," ","-")&"#")))," "),"#"," "),last,TAKE(arr,,-2),first,TEXTJOIN(" ",1,DROP(arr,,-2)),IF(nm="","",IF(LEN(nm)-LEN(SUBSTITUTE(nm," ",""))+1=2,HSTACK(last),HSTACK(first,last))))
Dynamic array formulas.

 

[RonOliver]

How in the world...?

 

[RonOliver]

Thanks so very much! I don't know how the formula works, but I'll try to get it step by step.

 

[Peter_SSs]

You're welcome. Hope it stands up to testing.

 

[jdellasala]

This post in the Lambda form may be of interest. REPLACESTRINGS.

 

[RonOliver]

You're welcome. Hope it stands up to testing.

It definitely does. I actually used an "IFERROR" before the last IF, so "Marten von Barnekow" wouldn't return an error. The only thing that's missing is for the formula to correctly recognize first and last names (there is no difference in form between firstname lastname1 lastname2 and firstname middlename lastname, which would wrongly lead the formula to categorizing the middle name as the first last name) but there really is no feasible way for a machine to do this, even with an incredibly exhaustive name list, so let's leave it there. Thanks so much for your help!

 

[RonOliver]

This post in the Lambda form may be of interest. REPLACESTRINGS.

Will definitely look into it in the next few days. Looks like the kind of thing that would help me tremendously.

 

[Peter_SSs]

so "Marten von Barnekow" wouldn't return an error.

It may not return the result that you would like, but that does not return an error for me even without that extra IFERROR.

RonOliver_1.xlsm
	C				G	H	I
22	Marten von Barnekow				Marten	von	Barnekow
Sheet1

Cell Formulas
Range	Formula
G22:I22	G22	=LET(t,TRIM(C22),nm,IF(ISNUMBER(FIND(",",t)),MID(t&" "&t,FIND(",",t)+2,LEN(t)-1),t),arr,SUBSTITUTE(TEXTSPLIT(REDUCE(nm,K$4:K$33,LAMBDA(s,r,SUBSTITUTE(s," "&r&" "," "&SUBSTITUTE(r," ","-")&"#")))," "),"#"," "),last,TAKE(arr,,-2),first,TEXTJOIN(" ",1,DROP(arr,,-2)),IF(nm="","",IF(LEN(nm)-LEN(SUBSTITUTE(nm," ",""))+1=2,HSTACK(last),HSTACK(first,last))))
Dynamic array formulas.

 

[RonOliver]

It may not return the result that you would like, but that does not return an error for me even without that extra IFERROR.

RonOliver_1.xlsm
	C				G	H	I
22	Marten von Barnekow				Marten	von	Barnekow
Sheet1

Cell Formulas
Range	Formula
G22:I22	G22	=LET(t,TRIM(C22),nm,IF(ISNUMBER(FIND(",",t)),MID(t&" "&t,FIND(",",t)+2,LEN(t)-1),t),arr,SUBSTITUTE(TEXTSPLIT(REDUCE(nm,K$4:K$33,LAMBDA(s,r,SUBSTITUTE(s," "&r&" "," "&SUBSTITUTE(r," ","-")&"#")))," "),"#"," "),last,TAKE(arr,,-2),first,TEXTJOIN(" ",1,DROP(arr,,-2)),IF(nm="","",IF(LEN(nm)-LEN(SUBSTITUTE(nm," ",""))+1=2,HSTACK(last),HSTACK(first,last))))
Dynamic array formulas.

That's weird. I added "van" to the preposition list and it gave me a #CALC! error, but that's fixed now, anyway.