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

**Table of Contents**hide

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

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

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

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

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

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

`=SUBSTITUTE(B4,"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 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

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

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

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

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.

Just provide `“”`

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

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