SUBSTITUTE function with a range of values to substitute

RonOliver

Board Regular
Joined
Aug 30, 2022
Messages
99
Office Version
  1. 365
Platform
  1. Windows
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
CDEF
4Jorge Guzmán PedrazaJorgeGuzmánPedraza
5David Tejero GarcíaDavidTejeroGarcía
6Tamara Bas MorenoTamaraBasMoreno
7Miguel Ureña PérezMiguelUreñaPérez
8Mónica Estadellas VacaMónicaEstadellasVaca
9Lena Viejo AlegreLenaViejoAlegre
10Francesco SchettinoFrancescoSchettino
11Marta Arenas CabrerizoMartaArenasCabrerizo
12Aura López AlcázarAuraLópezAlcázar
13Javier Alarcón AznarJavierAlarcónAznar
14Nabil HrairaNabilHraira
15Javier Fernández ParraJavierFernándezParra
16Fátima Andrade BayarriFátimaAndradeBayarri
17Noemi Caravantes FenollosaNoemiCaravantesFenollosa
18Gabriela López-Moreno del PinoGabrielaLópez-Morenodel Pino
19Carlos de la Torre RamírezCarlosde-la TorreRamírez
20Juan Antonio García GarcíaJuan AntonioGarcíaGarcía
21María de los Ángeles de Ojeda del PinoMaría de-los Ángelesde Ojedadel Pino
Separadora
Cell Formulas
RangeFormula
D15:F21,D14:E14,D11:F13,D10:E10,D4:F9D4=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!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Try this

RonOliver_1.xlsm
CGHIJK
4Jorge Guzmán PedrazaJorgeGuzmánPedrazay
5David Tejero GarcíaDavidTejeroGarcíaY
6Tamara Bas MorenoTamaraBasMorenoi
7Miguel Ureña PérezMiguelUreñaPérezI
8Mónica Estadellas VacaMónicaEstadellasVacadels
9Lena Viejo AlegreLenaViejoAlegreDels
10Francesco SchettinoFrancescoSchettinode les
11Marta Arenas CabrerizoMartaArenasCabrerizoDe les
12Aura López AlcázarAuraLópezAlcázarels
13Javier Alarcón AznarJavierAlarcónAznarEls
14Nabil HrairaNabilHrairales
15Javier Fernández ParraJavierFernándezParraLes
16Fátima Andrade BayarriFátimaAndradeBayarriDe La
17Noemi Caravantes FenollosaNoemiCaravantesFenollosaDe Las
18Gabriela López-Moreno del PinoGabrielaLópez-Morenodel PinoDe Los
19Carlos de la Torre RamírezCarlosde-la TorreRamírezDel
20Juan Antonio García GarcíaJuan AntonioGarcíaGarcíaDe
21María de los Ángeles de Ojeda del PinoMaría de-los Ángelesde Ojedadel PinoEl
22La
23Los
24Las
25de la
26de las
27de los
28de
29del
30el
31la
32los
33las
Sheet1
Cell Formulas
RangeFormula
G15:I21,G14:H14,G11:I13,G10:H10,G4:I9G4=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.
 
Upvote 0
Solution
Thanks so very much! I don't know how the formula works, but I'll try to get it step by step. :)
 
Upvote 0
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! :):)
 
Upvote 0
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
CGHI
22Marten von BarnekowMartenvonBarnekow
Sheet1
Cell Formulas
RangeFormula
G22:I22G22=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.
 
Upvote 0
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
CGHI
22Marten von BarnekowMartenvonBarnekow
Sheet1
Cell Formulas
RangeFormula
G22:I22G22=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. :)
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,195
Members
453,021
Latest member
pingpong7117

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top