How to use the SUBSTITUTE function in Microsoft Excel

You may want to do some basic operations you can perform in programs like Word, which allows you to find and replace specific words. However, as you can tell, you must use some functions to make the same operation in your worksheet when working in a spreadsheet.

That’s why you should learn how to use the SUBSTITUTE function in Microsoft Excel!

What is the SUBSTITUTE function?

First and foremost, the SUBSTITUTE function in Excel replaces a particular text or character within a given string. Hence, you can use it o replace all occurrences of a specific text or character or only the first occurrence, depending on how you specify the function.

When should you use it?

There are various situations where you can find this function handy. But these are the top 5:

1. Cleaning up data: If you have Excel data containing unwanted characters or symbols, you can use the SUBSTITUTE function to remove them. For example, if you have a phone numbers list containing dashes or parentheses, you can use SUBSTITUTE to remove them and format them uniformly.
2. Reformatting text: If you have text in Excel that needs to be reformatted, you can use SUBSTITUTE to change it. For example, if you have a list of names in uppercase, you can use SUBSTITUTE to convert them to the proper case.
3. Editing text: If you have to edit a text in Excel, you can use SUBSTITUTE to make changes. For example, if you have a list of addresses that need to have the street name corrected, you can use SUBSTITUTE to replace the incorrect street name with the correct one.
4. Searching for specific text: When searching for a particular text within a string of text or numbers, you can use SUBSTITUTE to find it. For example, if you have a list of product names and need to see all the products containing the word "green," you can use SUBSTITUTE to search for "green" in each product name.

Overall, the SUBSTITUTE function can be a powerful tool for data cleaning, formatting, and editing in Excel. By using it, you can save time and ensure that your data is consistent and accurate.

Syntax:

Because this is an Excel function, the SUBSTITUTE operation works like almost every formula. That’s why you need to enter the syntax following this base:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Where:

• "text" is the text or cell reference containing the original information you want to modify.
• "old_text" is the text or character you want to replace within the original string.
• "new_text" is the replacement text or character that will replace the old text.
• "instance_num" is an optional argument that specifies which occurrence of the old text you want to replace. If you omit this argument, Excel will replace all the criteria you selected in the original text.

Examples:

Of course, you know in which situation you can use this great function. However, let's consider a few examples to understand the syntax and usage of the SUBSTITUTE function.

Example 1: Replace a particular text.

Suppose you have a string in cell A1 that reads, "I love pizza, and pizza is my favorite food." You want to replace the word "pizza" with "sushi." You can use the following formula in cell B1:

=SUBSTITUTE(A1; “pizza”; “sushi”).

Example 2: Replace a single character.

On the other hand, if you have in cell A1 that reads “1,001” but you want to remove the comma from the number, You can use the following formula in cell B1:

=SUBSTITUTE(A1; “,”; “”).

Notice how Excel will replace all occurrences of "," in the cell with an empty string (because the double quote marks indicate that you want to remove everything), effectively removing the comma from the number.

Example 3: Replace a specific instance of a text.

When you have a particular text in a cell but only want to replace the second occurrence of a word, you can use the following formula: 

=SUBSTITUTE(A1; “the”; “a”;2).

Note: if you put a capital letter, Excel will take it as another different word.

In conclusion, the SUBSTITUTE function in Excel is a powerful tool that you can use to replace specific text or characters within a given string. You can easily modify lines and rows of data in your Excel spreadsheets by understanding its syntax and usage.

Do you want to know more?

You can combine the SUBSTITUTE function with the FIND operation. And, if you are interested in learning more about other tools, try our Excel course here at Skillademia!