Replace multiple characters in Excel

This is a quick tutorial on how to replace characters from a text string in Microsoft Excel. The SUBSTITUTE function comes in handy when creating email addresses for example and you have names with letters like ä, ö or ü. These letters need to be substituted with a, o and u.



Syntax

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

Substitute one letter


So, asuming we have a Georg Müller in cell A1, we would do this to substitute the "ü" with "u":

=SUBSTITUTE(A1;"ü";"u")
Note: Depending on your regional settings, you might have to use a comma (,) as a separator instead of semicolon (;):

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

Substitute multiple letters


So if we have a name like Kjäell Löhn in cell A1, we would do this to substitute the "ä" with "a" and "ö" with "o": 
=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")

Explanation

We first replace the letter "ä" with "a" with SUBSTITUTE(A1;"ä";"a"). Then we use this function as the source text to replace the letters "ö" with "o" like so: SUBSTITUTE(SUBSTITUTE(A1;"ä";"a");"ö";"o").

If you need to substitute even more letters, like the "ü" with "u", you would use the outcome of previous two functions as source text in a new SUBSTITUTE function:
=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.

3 comments:

  1. WOW, The Excel How To - Replace multiple characters: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE...

    Do you know BTW, that repeating a functions has a limit?

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  2. AWS Training in Bangalore - Live Online & Classroom
    myTectra Amazon Web Services (AWS) certification training helps you to gain real time hands on experience on AWS. myTectra offers AWS training in Bangalore using classroom and AWS Online Training globally. AWS Training at myTectra delivered by the experienced professional who has atleast 4 years of relavent AWS experince and overall 8-15 years of IT experience. myTectra Offers AWS Training since 2013 and retained the positions of Top AWS Training Company in Bangalore and India.


    IOT Training in Bangalore - Live Online & Classroom
    IOT Training course observes iot as the platform for networking of different devices on the internet and their inter related communication. Reading data through the sensors and processing it with applications sitting in the cloud and thereafter passing the processed data to generate different kind of output is the motive of the complete curricula. Students are made to understand the type of input devices and communications among the devices in a wireless media.

    ReplyDelete