Hi!
I'm trying to get this tool to join the strings of text and then delete certain prepositions from the names (if F2 has the word "Delete"):
My problem is each formula has the list of prepositions that I'd like to remove from the resulting string of text, which makes it uncomfortable when I want to add new prepositions to the list. For that reason, I have created an expandable list of prepositions in another sheet (named "Sheet2")
My question is: how can I fix the formula so I can stop stacking substitute functions and start using a list of prepositions that I can expand if need be? If possible, I need a formula that does exactly the same thing: delete the prepositions from the names. Any suggestions will be appreciated!
I'm trying to get this tool to join the strings of text and then delete certain prepositions from the names (if F2 has the word "Delete"):
| Book1 | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | Jorge | Guzmán | Pedraza | Jorge Guzmán Pedraza | ||||
| 2 | David | García | García | David García García | Delete | |||
| 3 | Tamara | Bas | Moreno | Tamara Bas Moreno | ||||
| 4 | Miguel | Ureña | Pérez | Miguel Ureña Pérez | ||||
| 5 | Mónica | Estadellas | Vaca | Mónica Estadellas Vaca | ||||
| 6 | Lena | Viejo | Alegre | Lena Viejo Alegre | ||||
| 7 | Francesco | Schettino | Francesco Schettino | |||||
| 8 | Marta | Arenas | Cabrerizo | Marta Arenas Cabrerizo | ||||
| 9 | Aura | López | Alcázar | Aura López Alcázar | ||||
| 10 | Javier | Alarcón | Aznar | Javier Alarcón Aznar | ||||
| 11 | Nabil | Hraira | Nabil Hraira | |||||
| 12 | Javier | Fernández | Parra | Javier Fernández Parra | ||||
| 13 | Fátima | Andrade | Bayarri | Fátima Andrade Bayarri | ||||
| 14 | Noemi | Caravantes | Fenollosa | Noemi Caravantes Fenollosa | ||||
| 15 | Gabriela | López-Moreno | del Pino | Gabriela López-Moreno Pino | ||||
| 16 | Carlos | de la Torre | Ramírez | Carlos Torre Ramírez | ||||
| 17 | Juan Antonio | García | García | Juan Antonio García García | ||||
| 18 | María de los Ángeles | Ojeda | de Morata | María Ángeles Ojeda Morata | ||||
Sheet1 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D1:D18 | D1 | =IF(AND(ISBLANK(A1:C1))," ",CONCAT(IF($F$2="Delete",IFERROR(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(A1," de los "," ")," de las "," ")," de la "," ")," del "," ")," de "," ")," el "," ")," la "," ")," los "," ")," las "," ")," y "," ")," i "," ")," dels "," ")," de les "," ")," les "," ")," els "," ")," De Los "," ")," De Las "," ")," De La "," ")," Del "," ")," De "," ")," El "," ")," La "," ")," Los "," ")," Las "," ")," Y "," ")," I "," ")," Dels "," ")," De Les "," ")," Les "," ")," Els "," "),A1),A1)," ",IF($F$2="Delete",TRIM(RIGHT(SUBSTITUTE(B1," ",REPT(" ",100)),100)),B1),IF(ISBLANK(C1),," "),IF($F$2="Delete",TRIM(RIGHT(SUBSTITUTE(C1," ",REPT(" ",100)),100)),C1))) |
My problem is each formula has the list of prepositions that I'd like to remove from the resulting string of text, which makes it uncomfortable when I want to add new prepositions to the list. For that reason, I have created an expandable list of prepositions in another sheet (named "Sheet2")
| Book1 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | AV | AW | AX | AY | AZ | |||
| 1 | y | Y | i | I | dels | Dels | de les | De les | De Les | els | Els | les | Les | de la | De la | De La | de las | De las | De Las | de los | De los | De Los | del | Del | de | De | el | El | la | La | los | Los | las | Las | ait | Ait | von | Von | van | Van | von der | Von der | Von Der | van der | Van der | Van Der | della | Della | dalla | Dalla | di | Di | ||
Sheet2 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||
My question is: how can I fix the formula so I can stop stacking substitute functions and start using a list of prepositions that I can expand if need be? If possible, I need a formula that does exactly the same thing: delete the prepositions from the names. Any suggestions will be appreciated!
