SUBSTITUTE Function in Excel: Explained

In this article, you will learn about the SUBSTITUTE formula and its uses in Excel.

‍

What is the SUBSTITUTE formula in Excel?

The SUBSTITUTE function in Excel is a powerful tool that allows you to replace specific text values or characters within a cell or a range of cells with new text values or characters. It is commonly used for data manipulation and editing tasks in Excel.

‍

Syntax of SUBSTITUTE Function in Excel

The syntax of the Excel SUBSTITUTE function is as follows:

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

text: This is the text or cell reference in which you want to make the substitution.

old_text: This is the text or characters to be replaced.

new_text: This is the new text or characters that will replace the old_text.

[instance_num] (optional): This argument specifies which occurrence of the old_text you want to replace. If omitted, all occurrences will be replaced.

‍

Important note about the SUBSTITUTE function in Excel

It's important to note that the SUBSTITUTE function in Excel is case-sensitive when finding the text or characters to replace. This means it will only replace text or characters that match the case of the specified old_text. To overcome this limitation, you can use additional functions like LOWER or UPPER to convert the text to a consistent case before using the SUBSTITUTE function.

‍

How to use the SUBSTITUTE function in Excel?

Understand the SUBSTITUTE function by going through some examples. The below sample data includes employee names and their designation. Now the objective is to replace all the text instances of “Sr.” in the designation with the text “Executive” as per the recent organizational changes.

‍

Mapping of the dataset with the syntax of the SUBSTITUTE function is as follows:

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

text: The input for this argument will be B2 as we need to make changes in the designation column.

old_text: The value of this attribute will be “Sr.” as we need to replace the same in employee designation records.

new_text: This argument will be “Executive” as employee designation needs to be updated with the same.

[instance_num] (optional): As this is an optional argument, we shall ignore it this time so that all instances are replaced.

Our complete function would look as the following:

=SUBSTITUTE(B2,"Sr.","Executive")

Now we need to apply this function in the corresponding cell C2 and press enter to generate the function result.

As you can observe, the old text “Sr.” has been replaced with the keyword “Executive”, and the new employee designation is updated under the Results column.  Now we shall utilize the drag feature of Excel to apply the same function to all other rows by holding the cursor on the bottom right corner of the cell until the + icon appears and then dragging the same to fill in all cells with the SUBSTITUTE formula.

‍

‍

You need to be cautious about the case of the text to be replaced, as the function is case-sensitive. Look at the following example. 

‍

As you can see in the image above, the function does not change the designation because the text to be replaced was entered in all CAPS, which does not match the text in the string.

‍

You can also utilize the SUBSTITUTE function to remove specific keywords. For instance, see the case below where you want to remove all “Sr.” text in the employee designation. 

To do so, you can simply write the formula below

=SUBSTITUTE(B11,"Sr.","")

The argument for new text is updated as “”, which replaces the old text with blank.

‍

As you can observe in the image above, all the employee designations are now updated as desired.

‍

When should you use the SUBSTITUTE Function in Excel? 

The SUBSTITUTE Function in Excel is particularly useful in several situations:

1. Find and replace: When you have a large dataset and you want to replace specific text values or characters with new ones throughout the dataset, the SUBSTITUTE function can save you time and effort. It automates finding and replacing text across multiple cells or ranges.
2. Cleaning data: If you are working with data that contain inconsistencies or errors, the SUBSTITUTE function can help you clean up the data. For example, you can use it to remove unnecessary characters, replace incorrect spellings, or standardize formatting within your dataset. 
3. Text manipulation: The SUBSTITUTE function is handy for manipulating text within cells. You can use it to replace specific characters or strings with alternative ones. For example, you can replace underscores (_) with spaces or replace certain abbreviations with their expanded forms.

‍

What is the difference between the SUBSTITUTE and REPLACE functions in Excel?

The SUBSTITUTE and REPLACE functions in Excel replace text in a cell or range of cells. However, there is a difference between the two functions. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE to replace any text that occurs in a particular location in a text string. REPLACE replaces part of a text string with a different text string based on the number of characters you specify. Conversely, when the position of the text is unknown and requires a search, you can utilize the SUBSTITUTE function to perform the replacement.