Unveiling the Secrets of Microsoft Excel: Effective Techniques for Leveraging the SUBSTITUTE Function

Quick Links

• What to Know When Using the SUBSTITUTE Function
• Replace Occurrences of a String Using SUBSTITUTE in Excel

To replace a string of text, numbers, or symbols, Microsoft Excel offers a function called SUBSTITUTE. This function replaces the specified string with your choice of string. We’ll show you how to use it in your spreadsheets.

Related: How to Replace Any Character with Newlines in Notepad++

What to Know When Using the SUBSTITUTE Function

When you use the SUBSTITUTE function, know that it’s case-sensitive and so you’ll have to use it accordingly. Also, you can’t specify wildcard entries in the function. You can use the function with your hard-coded values as well as cell references .

The function even lets you choose the instances of your specified string to change. This way, if you only want to change the first occurrence of a string, you can do so.

Replace Occurrences of a String Using SUBSTITUTE in Excel

To start using the function, open your spreadsheet with Microsoft Excel.

In the spreadsheet, select the cell in which you want to display the result. In the below example, we’ll replace

    `HTG`

with

    `How-To Geek`

.

In your selected cell, type the following function and press Enter.

In this function:

• B2: It’s the cell with the content that you want to replace.
• HTG: This is the original string that you want to find and replace with the new string.
• How-To Geek: This is the new string that will replace the old string.

=SUBSTITUTE(B2,”HTG”,”How-To Geek”)

You’ll see that the function has replaced the values as defined in the arguments.

Another scenario where you may want to use this function is when you want to change the country code for phone numbers. For example, if you have a list of phone numbers containing +91 as the country code, you can use the SUBSTITUTE function to make all these phone numbers use +1 as the country code.

To do so, use the SUBSTITUTE function with the following arguments:

=SUBSTITUTE(B2,”91”,”1”,1)

As you can see, in the above function, we’ve specified 1 at the end. This tells the function to only change the first occurrence of 91 to 1. If the remaining numbers in a phone number contain 91, the function won’t change that. This helps you avoid ending up with incorrect phone numbers.

And that’s how you use Excel’s SUBSTITUTE function to change various strings in your spreadsheets.

Another way to change your spreadsheet’s contents is by using Excel’s find and replace feature . Check out our guide on that if you’re interested.

Related: How to Find and Replace Text and Numbers in Excel

Also read: