Hello! I am an education specialist for Excel beginners. Today, we will explore various examples of utilizing the SUBSTITUTE function by nesting it. The SUBSTITUTE function is a function in Excel that replaces strings, providing the function to replace specific strings with other strings. Let’s learn how to use the SUBSTITUTE function through simple examples together!
Example 1: Changing the Domain in Email Addresses
As the simplest example, let’s change the domain in email addresses. Let’s examine the process of using the SUBSTITUTE function to change the ‘@gmail.com’ domain to ‘@outlook.com’.
| Email Address | Modified Address |
|---|---|
| johndoe@gmail.com | =SUBSTITUTE(A2, “@gmail.com”, “@outlook.com”) |
Example 2: Standardizing Phone Number Formats
As the second example, let’s make the phone number format consistent. Let’s use the SUBSTITUTE function to change the international phone number format ‘+82-010-1234-5678’ to the domestic phone number format ‘010-1234-5678’.
| International Phone Number | Domestic Phone Number |
|---|---|
| +82-010-1234-5678 | =SUBSTITUTE(SUBSTITUTE(A2, “+82-“, “”), “-“, “-“) |
Example 3: Removing Special Characters
For the final example, let’s look at how to remove special characters from text. Let’s examine the process of removing all spaces from a sentence using the SUBSTITUTE function.
| Original Text | Special Characters Removed |
|---|---|
| This, is; a. sample! text? | =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, “,”, “”), “;”, “”), “.”, “”), “!”, “”) |