# Substitute from a list in another sheet



## RonOliver (Dec 22, 2022)

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"):

Book1ABCDEF1JorgeGuzmánPedrazaJorge Guzmán Pedraza2DavidGarcíaGarcíaDavid García GarcíaDelete3TamaraBasMorenoTamara Bas Moreno4MiguelUreñaPérezMiguel Ureña Pérez5MónicaEstadellasVacaMónica Estadellas Vaca6LenaViejoAlegreLena Viejo Alegre7FrancescoSchettinoFrancesco Schettino8MartaArenasCabrerizoMarta Arenas Cabrerizo9AuraLópezAlcázarAura López Alcázar10JavierAlarcónAznarJavier Alarcón Aznar11NabilHrairaNabil Hraira12JavierFernándezParraJavier Fernández Parra13FátimaAndradeBayarriFátima Andrade Bayarri14NoemiCaravantesFenollosaNoemi Caravantes Fenollosa15GabrielaLópez-Morenodel PinoGabriela López-Moreno Pino16Carlosde la TorreRamírezCarlos Torre Ramírez17Juan AntonioGarcíaGarcíaJuan Antonio García García18María de los ÁngelesOjedade MorataMaría Ángeles Ojeda MorataSheet1Cell FormulasRangeFormulaD1:D18D1=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")

Book1ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZ1yYiIdelsDelsde lesDe lesDe LeselsElslesLesde laDe laDe Lade lasDe lasDe Lasde losDe losDe LosdelDeldeDeelEllaLalosLoslasLasaitAitvonVonvanVanvon derVon derVon Dervan derVan derVan DerdellaDelladallaDalladiDiSheet2

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!


----------



## Peter_SSs (Dec 23, 2022)

RonOliver said:


> 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?


The same way we did the same thing here? SUBSTITUTE function with a range of values to substitute


----------



## StephenCrump (Dec 23, 2022)

Another way, only lightly tested:

ABCD1de losde ladelde23GabrielaLópez-Morenodel PinoGabriela López-Moreno Pino4Carlosde la TorreRamírezCarlos Torre Ramírez5Juan AntonioGarcíaGarcíaJuan Antonio García García6María de los ÁngelesOjedade MorataMaría Ángeles Ojeda Morata7AAA de BBBCCC de la DDDde EEEEAaa Bbb Ccc Ddd Eeee8Sheet1Cell FormulasRangeFormulaD3:D7D3=TEXTJOIN(" ",,DropPrepositions(A3:C3,A$1:D$1))

*DropPrepositions: *=LAMBDA(s,list,LET(N,COUNTA(list),sNew,SUBSTITUTE(" "&UPPER(TRIM(s))&" "," "&INDEX(UPPER(list),1)&" "," "),IF(N=1,TRIM(PROPER(sNew)),DropPrepositions(sNew,DROP(list,,1)))))

But you need to be careful to get your list in the right order.  Here's a wrong way:

ABCD1dede losde ladel23GabrielaLópez-Morenodel PinoGabriela López-Moreno Pino4Carlosde la TorreRamírezCarlos La Torre Ramírez5Juan AntonioGarcíaGarcíaJuan Antonio García García6María de los ÁngelesOjedade MorataMaría Los Ángeles Ojeda Morata7AAA de BBBCCC de la DDDde EEEEAaa Bbb Ccc La Ddd Eeee8Sheet1Cell FormulasRangeFormulaD3:D7D3=TEXTJOIN(" ",,DropPrepositions(A3:C3,A$1:D$1))


----------



## RonOliver (Dec 23, 2022)

Peter_SSs said:


> The same way we did the same thing here? SUBSTITUTE function with a range of values to substitute


Yeah, I need that exact thing. The problem is I tried to use the formula (not c&p, but actually adapt it so it would work) and I couldn't get it to work. Tried TEXTJOIN, changing the LAMBDA, completely tearing it apart and starting from scratch...


----------



## RonOliver (Dec 23, 2022)

StephenCrump said:


> Another way, only lightly tested:
> 
> ABCD1de losde ladelde23GabrielaLópez-Morenodel PinoGabriela López-Moreno Pino4Carlosde la TorreRamírezCarlos Torre Ramírez5Juan AntonioGarcíaGarcíaJuan Antonio García García6María de los ÁngelesOjedade MorataMaría Ángeles Ojeda Morata7AAA de BBBCCC de la DDDde EEEEAaa Bbb Ccc Ddd Eeee8Sheet1Cell FormulasRangeFormulaD3:D7D3=TEXTJOIN(" ",,DropPrepositions(A3:C3,A$1:D$1))
> 
> ...


I'm going to test this thoroughly. Thanks for your help! Give me a few hours.


----------



## Peter_SSs (Dec 23, 2022)

See if this is it.

RonOliver_1.xlsmABCDEFK1JorgeGuzmánPedrazaJorge Guzmán Pedraza2DavidGarcíaGarcíaDavid García GarcíaDeletede los3TamaraBasMorenoTamara Bas Morenode las4MiguelUreñaPérezMiguel Ureña Pérezde la5MónicaEstadellasVacaMónica Estadellas Vacadel6LenaViejoAlegreLena Viejo Alegrede7FrancescoSchettinoFrancesco Schettinoel8MartaArenasCabrerizoMarta Arenas Cabrerizola9AuraLópezAlcázarAura López Alcázarlos10JavierAlarcónAznarJavier Alarcón Aznarlas11NabilHrairaNabil Hrairay12JavierFernándezParraJavier Fernández Parrai13FátimaAndradeBayarriFátima Andrade Bayarridels14NoemiCaravantesFenollosaNoemi Caravantes Fenollosade les15GabrielaLópez-Morenodel PinoGabriela López-Moreno Pinoles16Carlosde la TorreRamírezCarlos Torre Ramírezels17Juan AntonioGarcíaGarcíaJuan Antonio García GarcíaDe Los18María de los ÁngelesOjedade MorataMaría Ángeles Ojeda MorataDe Las19De La20Del21De22El23La24Los25Las26Y27I28Dels29De Les30Les31ElsSheet2Cell FormulasRangeFormulaD1:D18D1=LET(nm,TEXTJOIN(" ",1,A1:C1),n,REDUCE(nm,K$2:K$31,LAMBDA(s,r,SUBSTITUTE(s," "&r&" "," "))),IF(F$2="Delete",n,nm))


----------



## RonOliver (Dec 23, 2022)

Peter_SSs said:


> See if this is it.
> 
> RonOliver_1.xlsmABCDEFK1JorgeGuzmánPedrazaJorge Guzmán Pedraza2DavidGarcíaGarcíaDavid García GarcíaDeletede los3TamaraBasMorenoTamara Bas Morenode las4MiguelUreñaPérezMiguel Ureña Pérezde la5MónicaEstadellasVacaMónica Estadellas Vacadel6LenaViejoAlegreLena Viejo Alegrede7FrancescoSchettinoFrancesco Schettinoel8MartaArenasCabrerizoMarta Arenas Cabrerizola9AuraLópezAlcázarAura López Alcázarlos10JavierAlarcónAznarJavier Alarcón Aznarlas11NabilHrairaNabil Hrairay12JavierFernándezParraJavier Fernández Parrai13FátimaAndradeBayarriFátima Andrade Bayarridels14NoemiCaravantesFenollosaNoemi Caravantes Fenollosade les15GabrielaLópez-Morenodel PinoGabriela López-Moreno Pinoles16Carlosde la TorreRamírezCarlos Torre Ramírezels17Juan AntonioGarcíaGarcíaJuan Antonio García GarcíaDe Los18María de los ÁngelesOjedade MorataMaría Ángeles Ojeda MorataDe Las19De La20Del21De22El23La24Los25Las26Y27I28Dels29De Les30Les31ElsSheet2Cell FormulasRangeFormulaD1:D18D1=LET(nm,TEXTJOIN(" ",1,A1:C1),n,REDUCE(nm,K$2:K$31,LAMBDA(s,r,SUBSTITUTE(s," "&r&" "," "))),IF(F$2="Delete",n,nm))


I clearly did something wrong, and I still don't know what it was. Thanks so much for helping me out!


----------



## Peter_SSs (Dec 23, 2022)

No problem. Glad to help.


----------

