Substitute characters in one column with characters in another column

[gravanoc]

I'm looking for a dynamic formula whose purpose is to replace accented characters in names with non-accented characters. The potential accented characters are in column D and their replacements are in column E. I want to avoid using VBA and, because the list of accented characters might grow in the future, I'd prefer a formula that adapts to new additions, so it wouldn't be a bunch of nested SUBSTITUTEs.

Ex: Aarón Gómez would become Aaron Gomez by referencing these columns.

 

[Eric W]

You can use the LAMBDA function to create a function to do what you want.

Open a new workbook. Go to the Formulas tab, and click on "Define Name". For the Name put "ReplaceChars" (no quotes). In the Refers to: box put:

=LAMBDA(str,table,out,
  IF(str="",out,
    ReplaceChars(MID(str,2,LEN(str)-1),
                 table,
                 out&IFERROR(VLOOKUP(LEFT(str),table,2,0),LEFT(str))
    )
  )
 )

Click OK.

Now you can use the function like this:

Book2
	A	B	C	D	E
1	Accented Letter	English Letter		Original	Changed
2	ó	o		Aarón Gómez	Aaron Gomez
3	á	a		Juanita Cardeño	Juanita Cardeno
4	ñ	n
5	Ñ	N
Sheet3

Cell Formulas
Range	Formula
E2:E3	E2	=ReplaceChars(D2,SwapTable,"")

Named Ranges
Name	Refers To	Cells
SwapTable	=Sheet3!$A$2:$B$5	E2:E3

I named the table in A2:B5 SwapTable, so that if you ever want to update the table, you can do so and not have to change the formulas. For more information about LAMBDA, see LAMBDA function - Microsoft Support