Need to change foreign accented characters to regular non-accented characters? I just encountered this problem today. I have a list of hundreds of thousands of city names from all over the world, and I needed to generate a list of these names without the foreign accented characters. Here’s how to get this done in just two seconds.

For example, you have the following the text in a cell “A1″:

“Montréal”

and you’d like to change “é” to simply “e”, so that it shows the following:

“Montreal”

You can use Excel’s SUBSTITUTE function to do this. However, Excel only allows up to 64 levels of nesting, so you need to split your formula into two.

First, enter this in cell “B1″:

`=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(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(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"á","a"),"é","e"),"í","i"),"ó","o"),"ú","u"),"ã","a"),"ê","e"),"â","a"),"é","e"),"è","e"),"î","i"),"ï","i"),"ç","c"),"ä","a"),"ö","o"),"ü","u"),"ß","ss"),"ş","s"),"ı","i"),"ğ","g"),"ę","e"),"ł","l"),"ń","n"),"ś","s"),"ż","z"),"ã","a"),"ầ","a"),"à","a"),"ậ","a"),"đ","d"),"ế","e"),"ì","i"),"í","i"),"ổ","o"),"ô","o"),"ư","u"),"ả","a"),"ế","e"),"ĩ","i"),"ợ","o"),"ồ","o"),"ạ","a"),"ứ","u"),"ý","y"),"ạ","a"),"é","e"),"ỳ","y"),"ế","e"),"ể","e"),"ệ","e"),"ù","u"),"ë","e"),".",""),"Ġ","g"),"ø","o"),"ñ","n"),"'",""),"ō","o"),"--","-"),"ħ","h"),"å","a")`

Then, enter this in cell “C1″:

`=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D3,"æ","ae"),"Ġ","g"),"Č","c"),"ě","e"),"ň","n"),"š","s"),"ě","e"),"ň","n"),"ž","z"),"ř","r"),"č","c"),"ġ","g")`

Cell “C1″ will display the altered name without any foreign accented characters.

The formula above takes into account foreign accents in the following languages:

- Spanish
- Italian
- Portuguese
- French
- German
- Turkish
- Polish
- Swedish
- Dutch
- Hungarian
- Vietnamese
- Czech
- Slovak
- Danish
- Finnish
- Romanian