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
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.
Replaces existing text with new text in a text string.
=SUBSTITUTE(text, old_text, new_text, [instance_num])
|The text to change|
|The text to be replaced|
|The text to replace old_text with|
|The text to replace old_text with|
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.
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.
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.
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).
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.
The SUBSTITUTE(B5,”-“,”*”) syntax replaces all the (–) signs with the (*) sign.
Finally, you get the outcome depicted in the image below.
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).
5. Word Count Using SUBSTITUTE Function
- So, in the very beginning, go to cell C5 and write up the following formula.
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.
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
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.
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.
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))
$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.
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|
If the function name is spelled incorrectly.
|Stay cautious while entering the function name.|
We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
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.