How to replace characters or words from any text in Excel

This is a quick tutorial on how to replace characters from a text string in Microsoft Excel. The substitute function is a very useful tool for replacing characters or words from strings of text. Here is an example how to use substitute to replace unwanted characters. 

This is how the syntax looks like:

SUBSTITUTE(text we want to modify; old text; new text)

How to substitute one character or word

So, if we have a name like George Müller in cell A1 and we want to substitute the ü with u. Type the following formula to cell B1:

=SUBSTITUTE(A1;"ü";"u")

In this function we are telling Excel to modify the text in A1, to look for the character "ü" and change it to "u". If you want to replace a word instead of a character, just change the "ü" and "u" to what ever you want. For example:

=SUBSTITUTE(A1;"Müller";"This is my new last name")

Note: Depending on your regional settings, you might have to use a comma (,) as a separator instead of semicolon (;), which would look like this:

=SUBSTITUTE(A1,"ü","u")

How to substitute multiple characters or words

If we have a name like Kjäell Löhn in cell A1 and we want to substitute the characters "ä", "ö" with "a" and "o", we need to chain multiple subsitute functions together.

It's easier to add the functions one at a time and build the whole formula piece by piece. So let's first replace the letter "ä" with "a":

=SUBSTITUTE(A1;"ä";"a")

Then we take that function and replace the letter "ö" with "o" from the result:

=SUBSTITUTE(SUBSTITUTE(A1;"ä";"a");"ö";"o")

Note: Depending on your regional settings, you might have to use a comma (,) as a separator instead of semicolon (;):

=SUBSTITUTE(SUBSTITUTE(A1,"ä","a"),"ö","o")