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:
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!
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!