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]

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

No, it isn't weird because you didn't say in post #7 that you had added anything to the prepositions list, so I didn't either.
In any case I assume you added "von" not "van". Or else you typed the wrong sample name in post #7.
I do get the error if I add "von" to the list.

but that's fixed now, anyway.