How to use SUBSTITUTE function in Excel (4 Examples)

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).

Overview - Excel SUBSTITUTE Function

Practice Workbook

You are welcome to download the practice workbook from the link below.

SUBSTITUTE Function

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.

Syntax - Excel SUBSTITUTE Function

Summary

Replaces existing text with new text in a text string.

Syntax

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

Arguments

Argument Required/Optional Description
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

Versions

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.

Text dataset 1- Excel SUBSTITUTE Function

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.

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

B4 stores the text and our target word is Sales. And the word will be changed to Cost. 

Replace in dataset 1 - Excel SUBSTITUTE Function

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

Replace in dataset 1 autofill - Excel SUBSTITUTE Function

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.

Dataset of instances example - Excel SUBSTITUTE Function

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

=SUBSTITUTE(B4,"s","S")

All the s will be turned into S. 

Result without instance num - Excel SUBSTITUTE Function

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

=SUBSTITUTE(B4,"s","S",2)

Result in instances example - Excel SUBSTITUTE Function

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.

Instance examples - Excel SUBSTITUTE Function

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.

Multiple replace data - Excel SUBSTITUTE Function

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.

Multiple replace example - Excel SUBSTITUTE Function

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.

Multiple replace example 2 - Excel SUBSTITUTE Function

For example, KDB required three replacements so we have used three SUBSTITUTE.

Use the number of SUBSTITUTE functions depending on the circumstances.

Multiple replace autofill - Excel SUBSTITUTE Function

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.

Word count data - Excel SUBSTITUTE Function

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

=(LEN(TRIM(B4))-LEN(SUBSTITUTE(B4," ","")))+1

Word count formula result - Excel SUBSTITUTE Function

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.

Word count autofill - Excel SUBSTITUTE Function

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

=SUMPRODUCT(LEN(TRIM(B4:B7))-LEN(SUBSTITUTE(B4:B7," ",""))+1)

Total word count formula result - Excel SUBSTITUTE Function

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

Select inside portion - Excel SUBSTITUTE Function

And press F9. You will see the array within SUMPRODUCT.

Select inside portion result- Excel SUBSTITUTE Function

The SUMPRODUCT function then adds them together and produces the final result.

3. Quick Notes

  1. You can input text directly into the function.

direct input - Excel SUBSTITUTE Function

The function will provide the output correctly.

direct input result - Excel SUBSTITUTE Function

  1. You can replace any text with nothing.

replace with nothing - Excel SUBSTITUTE Function

Just provide “” at the new_text field.

replace with nothing result- Excel SUBSTITUTE Function

  1. While using instance_num, you have to provide a value greater than 0.

instance num less than 0 - Excel SUBSTITUTE Function

If you provide 0 or negative value, there will arise an error called #VALUE!.

instance num less than 0 result - Excel SUBSTITUTE Function

Conclusion

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.

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

ExcelDemy
Logo