How to replace characters or words from any text in Excel

By: Ilkka Mäkinen | Published: 8 April, 2016 | 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")

 

Subscribe to our newsletter

Get the latest expert advice from our blog on Excel, WordPress, search engine optimization, Google analytics and Adwords straight to your email.

 

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!

By: Ilkka Mäkinen | Published: 8 April, 2016 | In: Excel

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.

Want to stop messing about and learn to use Excel efficiently?

Save your spot in our Excel Essentials course and learn how to make Excel rock!

Terms & Conditions

Conditions: Minimum number of participants is 3 persons on each course. If the course you are enrolling in does not reach the minimum number of participants it will be cancelled or postponed. We will inform enrolled participants in case there are not enough participants on a course.

Cancellations and substitutes: a) Cancellations confirmed more than 21 days prior to the date of the course are fully refundable and free of charge. Cancellations are to be made in writing to info@talentwire.eu. 

b) Cancellations made within 21 days of the date of the course may not be cancelled without incurring course charges fully.

c) Substitutes may be made at any time free of charge. TalentWire should be informed of any changes.

Pricing and invoicing: Payment for each course is to be received no later than 14 days prior to the course start date. We will send an invoice after your booking has been confirmed. Inform us of any special arrangements you require for your invoices.

All prices are as stated and are exclusive of VAT which will where appropriate be charged at the current rate.

×