SUBSTITUTE Function in Excel – A Text Function

In one of our earlier blogs, we learned the use of REPLACE Function. The REPLACE function replaces a set of characters in a text string with another set of characters based on the location and length. The SUBSTITUTE Function in excel does the same job but in a different manner.

So let’s start learning the SUBSTITUTE Function.

When to Use the SUBSTITUTE Function in Excel?

The SUBSTITUTE Function in Excel substitutes a set of characters in a text string with another set of characters. It searches for the old string within the text. If there are multiple instances of the old text in the text, then either all the instances of old text are replaced with a new text string or we can specify which instance to replace.

An important point to mark is that the function automatically searches for the old text within the text string and we need not look for its position.

We can use the REPLACE Function if we want to replace the characters based on their location in the text string. Use the FIND Function to get the location of a character in the text.

Syntax and Arguments

The following points contain important points to note about the inputs required by SUBSTITUTE Function in Excel.

• text – This is the required text string, a part of which needs to be substituted.
• old_text – This is the set of characters that we want to substitute in the text string.
• new_text – Here we supply the new set of characters to replace with the instances of old_text in the original text.
• [instance_num] – If there are multiple instances of old text in the original text string, then by default, the function substitutes all the instances of old text with new text. In the second situation, we can explicitly specify which instance of old text to replace using the instance_num argument.

Things to Remember about SUBSTITUTE Function

The following are some important points about SUBSTITUTE Function.

• If the original text does not contain the text we want to replace, the whole text string is returned as result. For example, =SUBSTITUTE(“excel”,”unlocked”,”aa”) returns excel.
• The SUBSTITUTE Function is case-sensitive. If you are looking to replace “ex” in “Excelunlocked”, then this is not going to happen with the SUBSTITUTE Formula.
• To replace any specific instance of old_text in the original text, use the [instance_num] argument.
• SUBSTITUTE Function does not support wild cards.

Examples to Learn SUBSTITUTE Function

In this part of the blog, we are going to use the SUBSTITUTE Function.

Example 1 – Removing Hyphen from Phone Numbers

let us suppose we have got the phone numbers in the format xxx-xxx-xxxx

We want to remove these hyphens among the digits of different phone numbers. Use the following SUBSTITUTE formula in cell B2.

=SUBSTITUTE(A2,"-","")

Select the range B2:B12 and press the Ctrl D key to copy the formula for all the phone numbers.

As a result, we have got the actual phone numbers in column B.

Explanation – We have supplied the original text which is the phone number in cell A2 as the first text argument of the SUBSTITUTE Formula. Since we wanted to replace all the hyphens in the phone number, we have passed the old_text argument as a hyphen in double quotes “-“. We want to remove the hyphens or simply replace them with an empty text string denoted by “as the new_text argument.

As a result, all the instances of hyphens in the phone number are replaced with an empty string. This removes the hyphens.

Example 2 – Substituting a Specific Instance of text

Let us suppose we have got the following product codes of a company’s product as follows.

The product code is in the format QP-10-YYYY where YYYY represents the year. The years have been written incorrectly and each beginning 1 in the code is to be substituted with 2. For example, the code QP-10-1017 when written correctly would be QP-10-2017.

Use the following SUBSTITUTE Formula to perform this task.

=SUBSTITUTE(A2,"1","2",2)

Copy the formula for all the codes and we get the result as follows.

As a result, we have got the correct codes.

Explanation – There is already a 1 preceding the Year in the product code. We only want to replace the 1 after that, which is contained in the year.

We do not want to change the 1 in the red box. The only purpose is to replace the second instance of 1 in the blue box. Therefore, we specify the fourth optional argument that contains the instance number to consider. We supply it as 2. As a result, the second instance of 1 is replaced with 2.

This brings us to the end of the SUBSTITUTE Function blog.

Thank you for reading.