There are several text functions in Excel to help you perform your desired tasks easily and swiftly. Today we are going to show you how to use a text function called: SUBSTITUTE. For this session, we are using Excel 2019, feel free to use yours (at least 2007).
You are welcome to download the practice workbook from the link below.
1. Basics of SUBSTITUTE
The SUBSTITUTE function is categorized under the TEXT functions in Excel. 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])
|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||Specifies which occurrence from old_text you want to replace with new_text|
Workable from Excel 2007
2. Uses of SUBSTITUTE
I. 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 for the respective strings.
Let’s write the formula for the first text.
B4 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 after. Changing the value in the respective parameters will achieve our desired changes.
II. Change at a particular instance
We can change a particular text from a specific position providing the instance value within SUBSTITUTE.
Our example dataset consists of several random texts.
Every row of the table contains redundant letters, this was for example purpose.
Let’s consider we want to replace lower case starting s of superstar from the first rows with upper case S.
If we write the below formula
All the s will be turned into S.
So we need to specify the position. Here our desired s is the 2nd s of the string. That’s why we need to provide 2 at the instance_num field.
Our formula will be
We have found the change. Only one s has been replaced by S.
Depending upon your preference set the old_text, new_text, and the instance_num value. You will get the desired result.
III. Multiple SUBSTITUTE for Multiple Changes
A single SUBSTITUTE function will help you changing one text at a time. If you want to change multiple texts at one go, then you need to use multiple SUBSTITUTE functions together.
Here to show examples we have brought a dataset of 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 the first one, our formula will be
=SUBSTITUTE(SUBSTITUTE(B4,"C","Cristiano "),"R","Ronaldo ")
This is a nested formula and you can see the result of this formula below.
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.
Here we have used two SUBSTITUTE if the number of changes increases you need to increase the number of the function as well.
For example, KDB required three replacements so we have used three SUBSTITUTE.
Use the number of SUBSTITUTE functions depending on the circumstances.
IV. Word Count using SUBSTITUTE
We can count the number of words present in a string using the SUBSTITUTE function though we need to get help from other functions also.
Our example dataset contains several random strings. We will count the number of words from there.
We need to use the LEN, and TRIM functions along with SUBSTITUTE. To know about the helping function, please visit the article; LEN
Our formula will be
We have found the number of words from this string.
SUBSTITUTE removes all spaces from the string, 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 eradicate 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.
Similar formula will provide the number of words for the rest of the texts.
If you need to find the total number of words from the range you need to use another function called SUMPRODUCT with our previously used formula.
To know about the function, visit this SUMPRODUCT article.
And our formula will be
Hope you have understood the inner calculations which we have discussed. This time we have provided a range so the inside portion of the SUMPRODUCT returned an array of words count for every row.
Select the inner portion
And press F9. You will see the array within SUMPRODUCT.
The SUMPRODUCT function then adds them together and produces the final result.
3. Quick Notes
- You can input text directly into the function.
The function will provide the output correctly.
- You can replace any text with nothing.
“” at the new_text field.
- While using instance_num, you have to provide a value greater than 0.
If you provide 0 or negative value, there will arise an error called #VALUE!.
That’s all for today. We have tried showing how you can use the SUBSTITUTE function. You can use the function to replace text from a string as well as several advanced operations like words count. Hope you will find this helpful.
Feel free to comment if anything seems difficult to understand. Let us know any of your SUBSTITUTE function-related scenarios where you have stuck, we are ready to help.