Transliteration in Excel: Tedious and Brief

Transliteration is when you have a string like cañon and you want to convert it to canon or canyon. You have a list of single characters and the strings you want to convert those to.

Unix has the tr utility, but Excel makes this tedious. Here's how I do it (this is a table that transliterates the accented character for all European languages into unaccented forms).

=LAMBDA(s, LET(table, {"á","a";"Á","A";"à","a";"À","A";"ä","a";"Ä","A";"ã","a";"Ã","A";"å","a";"Å","A";"â","a";"Â","A";"æ","ae";"Æ","AE";"ç","c";"Ç","C";"ð","d";"Ð","D";"é","e";"É","E";"è","e";"È","E";"ë","e";"Ë","E";"ê","e";"Ê","E";"í","i";"Í","I";"ì","i";"Ì","I";"ï","i";"Ï","I";"î","i";"Î","I";"ñ","n";"Ñ","N";"ó","o";"Ó","O";"ò","o";"Ò","O";"ö","o";"Ö","O";"õ","o";"Õ","O";"ô","o";"Ô","O";"ø","o";"Ø","O";"œ","oe";"Œ","OE";"ß","ss";"ẞ","SS";"þ","th";"Þ","TH";"ú","u";"Ú","U";"ù","u";"Ù","U";"ü","u";"Ü","U";"û","u";"Û","U";"ý","y";"Ý","Y";"ÿ","y";"Ÿ","Y"},CONCAT(MAP(REGEXEXTRACT(s,".",1),LAMBDA(c, XLOOKUP(TRUE,EXACT(c,TAKE(table,,1)),DROP(table,,1),c)))) )) 

The table looks like this:

https://preview.redd.it/pguoqw54s9mg1.png?width=273&format=png&auto=webp&s=106957dedc8e5b6ac4a2b7b711495837fe409da6

It has what you're looking for on the left and what you want it to become on the right.

Obviously you could do this with 66 calls to SUBSTITUTE, but that's not going to be much fun!

What I do is break the string into separate characters with REGEXTRACT (an idiom everyone should know!) and then call MAP, which uses XLOOKUP to find the matching character. CONCAT just zips the results back into a single string.

The table format makes it easy to add or remove character pairs, since they're always right next to each other.

It is annoying that XLOOKUP cannot be made to do a case-sensitive match. That forces me to use EXACT and then search for TRUE, which means we cannot sort the table and have XLOOKUP use binary search (not that it's in the right order at the moment anyway), which would speed it up a lot.

As ever, I'd love to know a better way to do this.