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.

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

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.

### 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**,

**, and**

*new_tex*t**values. You will get the desired result (see the image).**

*instance_num*### 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.

### 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

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.

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.

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

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

## Practice Section

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.

## 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**