How to Use the SUBSTITUTE Function in Excel (7 Examples)

Get FREE Advanced Excel Exercises with Solutions!

The SUBSTITUTE function is one of the text functions available in Excel that replaces the existing text with new text in a string. If you want to replace your text data with another text, the SUBSTITUTE function becomes a savior. In this article, we will show you some uses of the SUBSTITUTE function with examples.

Before going to the discussion, look at the quick view image we have attached below to get a basic idea about the working principles of the SUBSTITUTE function.

Excel SUBSTITUTE Function: Quick View

Quick View image


Introduction to SUBSTITUTE Function in Excel

The SUBSTITUTE function is categorized under the Text functions category in Excel’s function library. The function replaces text in a given string upon matching.

SUBSTITUTE function syntax in Excel

Summary:

Replaces existing text with new text in a text string.

Syntax:

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

Argument Explanation:

Argument Required/Optional Explanation

text

Required

The text to change

old_text

Required

The text to be replaced

new_text

Required

The text to replace old_text with

instance_num

Optional

The text to replace old_text with

Versions:

Workable from Excel 2007.


How to Use SUBSTITUTE Function in Excel: 7 Examples

We have discussed 7 basic examples of the SUBSTITUTE function. Also, we will show you the difference between the SUBSTITUTE and the REPLACE functions.

Regardless, we have used the Microsoft 365 version. You can use any other version at your convenience.


1. Make Changes to the Original String

The description of the SUBSTITUTE function might let you understand that you can change the string as per your preferences. To show you examples, we have brought a dataset consisting of a few random texts.

Making Changes to the Original String

For simplicity, we have included the aiming changes. For replacing the text, all we need to do is set the text to change and the text to change with the respective strings.

Now, write the formula in cell D5.

=SUBSTITUTE(B5,"Sales","Cost")

Here, B5 stores the text, and our target word is “Sales”. And the word will be changed to “Cost”.

Our formula has provided the output we were looking for. Changing the value in the respective parameters will achieve our desired changes.

Excel SUBSTITUTE function to change the original string


2. Change a Particular Instance

We can change a particular text from a specific position by providing the instance value within the SUBSTITUTE function. Every row of the table contains redundant letters, this was for example purposes. Let’s consider we want to replace the lower case starting s of superstar from the first rows with upper case S.

  • Firstly, we write the formula in cell C5.
=SUBSTITUTE(B5, "s", "S")

This function changes all the “s” to “S”.

We don’t want it. So, we need to specify the position. Here is the string’s second “s.” That’s why we need to provide 2 in the instance_num field.

  • So, secondly, we type the formula in cell C5.
=SUBSTITUTE(B5, "s", "S",2)

We have found the change. Only one “s” has been replaced by “S”. Depending upon your preference, set the old_text, new_text, and instance_num values. You will get the desired result (see the image).

Excel SUBSTITUTE Function to change a Particular Instance


3. Change Any Special Character

Sometimes, you may need to replace the special character in your dataset with another character. Look at our dataset. We want to change the () sign with the (*) sign. We have used the SUBSTITUTE function here.

  • Initially, move to cell C5 and insert the formula.
=SUBSTITUTE(B5,"-","*")

The SUBSTITUTE(B5,”-“,”*”) syntax replaces all the () signs with the (*) sign.

Finally, you get the outcome depicted in the image below.

Excel SUBSTITUTE function for changing a special character


4. Nested SUBSTITUTE Function for Multiple Changes

A single SUBSTITUTE function will help you change one text at a time. If you want to change multiple texts at once, then you need to use multiple SUBSTITUTE functions together.

To show examples, we have brought a dataset of a few footballers with their shorter nicknames and numbers.

We are set to find the full name from the short name. The short name consists of their respective first and last name. We need to replace the initials with the full portion. For doing this, we have used the Nested SUBSTITUTE function.

  • Firstly, go to cell C5 and write up the formula.
=SUBSTITUTE(SUBSTITUTE(B5,"C","Cristiano "),"R","Ronaldo ")

The inner SUBSTITUTE function operated first. It changed CR7 to Cristiano R7 and handed over the text to the outer SUBSTITUTE. Then the outer SUBSTITUTE replaced R with Ronaldo and produced the final output (see the below image).

Nested SUBSTITUTE function for Multiple Changes in Excel


5. Word Count Using SUBSTITUTE Function

We can count the number of words present in a string using the SUBSTITUTE function, though we also need help from other functions. We need to use the LEN, and TRIM functions along with SUBSTITUTE.

  • So, in the very beginning, go to cell C5 and write up the following formula.
=(LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ","")))+1

Formula Explanation:

The SUBSTITUTE function removes all spaces from the string, and then LEN calculates the length without spaces.

This number is then subtracted from the length of the text with spaces. We have used TRIM to eliminate any spaces at the beginning or end of the string.

And finally, 1 is added to the result, since the number of words is the number of spaces + 1.

  • Then, press ENTER and drag it down for the other cells.

Word Count Using SUBSTITUTE Function in Excel

If you need to find the total number of words in the range, you need to use another function called the SUMPRODUCT function with our previously used formula.

  • Eventually, the formula looks like
=SUMPRODUCT(LEN(TRIM(B5:B8))-LEN(SUBSTITUTE(B5:B8," ",""))+1)

We hope you have understood the inner calculations we have discussed. This time we have provided a range, so the inside portion of the SUMPRODUCT returned an array of word counts for every row.

Finally, you get your total word count, as shown in the above image.


6. Retrieve Specified String Based on the Delimiter

In this case, we have taken a dataset where we put the Product with its Product Id. Now we want to retrieve the last part of the text. We complete the operation based on the delimiter.

  • Primarily, go to cell D5 and insert the formula.
=RIGHT(C5,LEN(C5)-FIND("*",SUBSTITUTE(C5,"-","*",LEN(C5)-LEN(SUBSTITUTE(C5,"-","")))))

Formula Explanation:

SUBSTITUTE(C5,”-“,””)→ Substitutes the (-) sign from the main value of C5.

  • Output → TY341DFZ

LEN(SUBSTITUTE(C5,”-“,””)→ counts the length of the string.

  • Output → 8

LEN(C5)-LEN(SUBSTITUTE(C5,”-“,””)→ Total length of the string subtracts from the string excluding (-).

  • Output → 10-8=2

SUBSTITUTE(C5,”-“,”*”,LEN(C5)-LEN(SUBSTITUTE(C5,”-“,””)→ This function converts the (-) to the (*). However, it substitutes one () for the last.

FIND(“*”,SUBSTITUTE(C5,”-“,”*”,LEN(C5)-LEN(SUBSTITUTE(C5,”-“,””))→ The FIND function searches for the text before the (*) sign as we insert the input (*).

  • Output → 7

LEN(C5)-FIND(“*”,SUBSTITUTE(C5,”-“,”*”,LEN(C5)-LEN(SUBSTITUTE(C5,”-“,””)))→ Subtracts from the total length of the string with the result of gained by the FIND function.

  • Output → 10-7=3

RIGHT(C5,LEN(C5)-FIND(“*”,SUBSTITUTE(C5,”-“,”*”,LEN(C5)-LEN(SUBSTITUTE(C5,”-“,””)))))→ The RIGHT function takes the output value of these functions and returns the text value.

  • Output → DFZ

Consequently, press ENTER and drag down the same formula to get the result shown in the image below.

Retrieving Specified String Based on the Delimiter


7. Use the SUBSTITUTE Function for Multiple Characters

In addition, we can change multiple texts with the SUBSTITUTE and INDEX functions. The INDEX function finds the value we want to change, and the SUBSTITUTE function then changes the string.

  • For doing this, go to the C5 cell and enter the following formula.
=SUBSTITUTE(SUBSTITUTE(B5, INDEX($B$13:$B$14,1), INDEX($C$13:$C$14,1)), INDEX($B$13:$B$14,2), INDEX($C$13:$C$14,2))

Here,

$B$13:$B$14 = The text we want to change.

$C$13:$C$14 = The text that we wanted after changing.

  • Then, press ENTER and drag down to get the result shown in the image below.

Using the SUBSTITUTE Function for Multiple Characters in Excel


SUBSTITUTE vs. REPLACE Function in Excel: What’s the Difference?

  • The SUBSTITUTE function is a better alternative to the REPLACE function. You can use the REPLACE function only when you know the start_num and num_chars arguments in numerical values. But while using the SUBSTITUTE function, you won’t even need to input any start_num or num_char arguments. You have to simply input the cell name, the word or the text to be substituted, and the substitute word.

Common Errors While Using the SUBSTITUTE Function

Error Cause of Error Solution

#NAME?

If the function name is spelled incorrectly.

Stay cautious while entering the function name.

Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Practice Section


Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


Conclusion

That’s all about today’s session. And these are some easy easy examples of the SUBSTITUTE function in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Visit our website ExcelDemy, a one-stop Excel solution provider, to find out about diverse kinds of Excel methods. Thanks for your patience in reading this article.


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shakil Ahmed
Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo