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.

Excel SUBSTITUTE Function

1. Basics of SUBSTITUTE Function

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


Replaces existing text with new text in a text string.


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


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


Workable from Excel 2007

2. Uses of SUBSTITUTE Function

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.


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

Replace in dataset 1

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

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


All the s will be turned into S. 

Result without instance num

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


Result in instances example

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

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

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

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

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

Read more: How to use TRIM function in Excel

Our formula will be

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

Word count formula result

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

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


Total word count formula result

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

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

Select inside portion result

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 - Substitute Function

The function will provide the output correctly.

direct input result

  1. You can replace any text with nothing.

replace with nothing

Just provide “” at the new_text field.

replace with nothing result

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

instance num less than 0

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

instance num less than 0 result


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.

Further Readings

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