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")
If you need to substitute even more letters, like the "ü" with "u", you can chain even more SUBSTITUTE functions:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"ä";"a");"ö";"o");"ü";"u")
Note: Depending on your regional settings, you might have to use a comma (,) as a separator instead of semicolon (;):
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"ä";"a");"ö";"o");"ü";"u")
Note: Substitute function is case sensitive, so you have to substitute capital letters separately.
That's it for this tutorial, you can learn more tips and the efficient use of Excel in our Excel courses. The Excel Essentials training course is perfect for for beginners and intermediate levels, the Excel Advanced course is the best match for those who want to become more advanced Excel users.
Until next time, happy coding!
Sign up to our newsletter
Expert advice on Excel, WordPress, SEO, Google analytics and Adwords. Sign up to our newsletter to get the latest posts directly into your inbox.