We often replace specific text values with other text values. In such scenarios, the **SUBSTITUTE** function appears very handy. In this article, I’ll demonstrate 7 ideal examples of how to use the **SUBSTITUTE** function in Google Sheets. Also, I’ll discuss 2 alternatives to the **SUBSTITUTE** function in Google Sheets.

## A Sample of Practice Spreadsheet

You can copy our practice spreadsheets by clicking on the following link. The spreadsheets contain an overview of the datasheet and an outline of the described examples to use the **SUBSTITUTE** function in Google Sheets.

## What Is SUBSTITUTE Function in Google Sheets?

The **SUBSTITUTE** function is a text function in Google Sheets that can replace any existing string with a new string.

#### Syntax

The syntax of the **SUBSTITUTE** function is as follows:

`SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])`

#### Argument

The arguments of the **SUBSTITUTE** function are the following:

Argument | Requirement | Function |
---|---|---|

text_to_search |
Required | The text where text substitution occurs. |

search_for |
Required | The string to be substituted. |

replace_with |
Required | The string that will substitute the existing string. |

[occurrence_number] |
Optional | The instance of the string to be substituted. |

#### Output

The formula **SUBSTITUTE(“L. Messi”, “L.”, “Lionel”)** will show **Lionel Messi** as output.

## 7 Ideal Examples to Use SUBSTITUTE Function in Google Sheets

First, let’s get used to our dataset. The dataset contains several old product IDs for an electronics shop. We’ll use the **SUBSTITUTE** function to replace these old product IDs with new product IDs according to various formats.

### 1. Applying SUBSTITUTE Function to Change Every Instance

While using the **SUBSTITUTE** function, if we don’t provide the optional argument (i.e. **occurrence_number**), then the **SUBSTITUTE** function replaces all occurrences. Let’s replace all the Hyphen (-) symbols with the Slash (/) symbol in our dataset.

__Steps:__

- First, select
**Cell C5**. - After that, type in the following formula-

`=SUBSTITUTE(B5,$B$12,$C$12)`

- After that, press the
**Enter**key to get the required value.

- To make the
**Fill Handle**icon visible, hover your mouse pointer above the right-bottom corner of**Cell C5**.

- Finally, use the
**Fill Handle**icon to copy the formula to other cells of**Column C**.

**Read More: How to Remove Everything after Character in Google Sheets**

### 2. Employing SUBSTITUTE Function to Change Particular Instance

Now, let’s provide the **occurrence_number** argument to the **SUBSTITUTE** function. We’ll change only the first instance of the Hyphen (-) symbol with Slash (/) symbol this time.

__Steps:__

- Select
**Cell C5**and type in the following formula-

`=SUBSTITUTE(B5,$B$12,$C$12,1)`

- Then, press the
**Enter**key to get the required output.

- Finally, use the
**Fill Handle**icon to copy the formula in other cells.

### 3. Implementing Nested SUBSTITUTE Function

If we require more than one change in a single cell, we can implement a nested **SUBSTITUTE** function to achieve our desired result. For this example, we’ll make the following two changes in our dataset. Now let’s apply a nested **SUBSTITUTE** function to implement the desired change.

__Steps:__

- To start, select
**Cell C5**first. - Afterward, type in the following formula-

`=SUBSTITUTE(SUBSTITUTE(B5,$B$13,$C$13),$B$12,$C$12)`

- Next, press
**Enter**key to get desired output.

__Formula Breakdown__

**SUBSTITUTE(B5,$B$13,$C$13)**

First, this **SUBSTITUTE** function replaces elements of **Cell B13** (**1227**) with elements of **Cell C13** (**1228**) in the string of **Cell B5**.

**SUBSTITUTE(SUBSTITUTE(B5,$B$13,$C$13),$B$12,$C$12)**

The output of the inner **SUBSTITUTE** function works as the **text_to_search** for the outer** SUBSTITUTE **function. After that, the outer **SUBSTITUTE** function changes all the Hyphen (-) symbols (element in **Cell B12**) with the Slash (/) symbol (element in **Cell C12**).

- Now, finally, use the
**Fill Handle**icon to copy the formula to other cells.

### 4. Combining SUBSTITUTE, LEFT, and IF Functions for Multiple Characters

If you need to replace multiple characters from the left or right side of the strings in a data range, then you can combine the **SUBSTITUTE** and **IF** functions with **LEFT** or **RIGHT **functions according to the requirement. For this example, we’ll change 2 characters from the left side of the strings in our data range in the following manner.

__Steps:__

- First, select
**Cell C5**and then type in the following formula-

`=SUBSTITUTE(B5,LEFT(B5,2),(IF(LEFT(B5,2)=$B$12,$C$12,$C$13)))`

- After that, press the
**Enter**key next to get the required result.

__Formula Breakdown__

**LEFT(B5,2)**

The** LEFT function** is used twice in our formula. In both instances, it returns the left 2 characters of the string in **Cell B5**.

**IF(LEFT(B5,2)=$B$12,$C$12,$C$13)**

Then, the **IF** function checks whether the left 2 characters of the string in **Cell B5** match the content of **Cell B12**. If a match is found then the **IF** function returns the content of **Cell C12**. Else, it returns the content of **Cell C13**.

**SUBSTITUTE(B5, LEFT(B5,2),(IF(LEFT(B5,2)=$B$12,$C$12,$C$13)))**

Finally, the **SUBSTITUTE** function replaces the left 2 characters of the string in **Cell B5** with the output returned by the **IF** function. Note that, you must keep the **IF** function in a set of parentheses, otherwise the formula won’t execute.

- Finally, use the
**Fill Handle**icon to copy the formula in the other cells of**Column C**.

**Read More: ****How to Use RIGHT Function in Google Sheets (6 Suitable Examples)**

### 5. Merging ARRAYFORMULA, SUBSTITUTE, RIGHT, and IF Functions for Multiple Characters

Instead of using the **Fill Handle** icon to copy our formula in other cells, we can use **the ARRAYFORMULA function** to perform a similar operation for a range. Let’s combine the** ARRAYFORMULA** function with **SUBSTITUTE** and **IF** functions, and instead of the **LEFT** function, use the **RIGHT** function to change characters from the right side this time.

__Steps:__

- Select
**Cell C5**first. - Afterward, type in the following formula-

`=ARRAYFORMULA(SUBSTITUTE(B5:B9,ARRAYFORMULA(RIGHT(B5:B9,2)),(IF(ARRAYFORMULA(RIGHT(B5:B9,2)=B12),C12,C13))))`

- Next, press the
**Enter**key to get desired output.

__Formula Breakdown__

**RIGHT(B5:B9,2)**

The **RIGH**T function is used twice in our formula. In both instances, it returns 2 characters from the right side of the strings in array **B5:B9**.

**ARRAYFORMULA(RIGHT(B5:B9,2))**

This **ARRAYFORMULA** function helps the non-array function **RIGHT** to deal with an array.

**IF(ARRAYFORMULA(RIGHT(B5:B9,2)=B12), C12, C13))**

Now, the **IF** function checks whether the 2 rightmost characters of the strings in range** B5:B9** match with the content of **Cell B12**. If a match is found then the **IF** function returns the content of **Cell C12**. Else, it returns the content of **Cell C13**.

**SUBSTITUTE(B5:B9,ARRAYFORMULA(RIGHT(B5:B9,2)),(IF(ARRAYFORMULA(RIGHT(B5:B9,2)=B12),C12,C13)))**

Afterward, the **SUBSTITUTE** function replaces the 2 rightmost characters of the strings in range **B5:B9** with the output returned by the subsequent **IF** function.

**ARRAYFORMULA(SUBSTITUTE(B5:B9,ARRAYFORMULA(RIGHT(B5:B9,2)),(IF(ARRAYFORMULA(RIGHT(B5:B9,2)=B12),C12,C13))))**

Finally, this **ARRAYFORMULA** function helps the non-array function **SUBSTITUTE** to deal with an array.

**Read More: How to Remove Text after Character in Google Sheets (5 Methods)**

### 6. Uniting SUBSTITUTE, RIGHT, LEN, and FIND Functions to Retrieve Specific String

We can also use the **SUBSTITUTE** function united with the **RIGHT**, **LEN**, and **FIND** functions to retrieve a specific string from a longer string. For example, let’s retrieve the brand names from the product IDs for the following dataset. Note that, the brand names don’t have equal string lengths. Hence, using only the **RIGHT** function won’t help.

__Steps:__

- Select
**Cell C5**first. - After that, type in the following formula-

`=RIGHT(B5,LEN(B5)-FIND("\",SUBSTITUTE(B5,"-","\",LEN(B5)-LEN(SUBSTITUTE(B5,"-","")))))`

- Then press the
**Enter**key to retrieve the required string.

__Formula Breakdown__

**LEN(B5)**

The **LEN** function returns the length of the string in **Cell B5**. The output here is **15**.

**SUBSTITUTE(B5,”-“,””)**

The **SUBSTITUTE** function replaces all the Hyphen (-) symbols from the string in **Cell B5**. The output here is **LP122701Asus**.

**LEN(SUBSTITUTE(B5,”-“,””))**

Then, this** LEN function** returns the length of the string returned by the **SUBSTITUTE** function. The output here is **12**.

**LEN(B5)-LEN(SUBSTITUTE(B5,”-“,””))**

This part of the formula subtracts the length of the string in **Cell B5** and the length of the string returned by the **SUBSTITUTE** function. The output here is **3**. This number indicates the number of Hyphen (-) symbols in the string of **Cell B5**.

**SUBSTITUTE(B5,”-“,”\”,LEN(B5)-LEN(SUBSTITUTE(B5,”-“,””)))**

Since we want to retrieve the string after the last Hyphen (-) symbol, we have substituted the last Hyphen (-) symbol with a Backslash (\) symbol using the **SUBSTITUTE** function. The output here is **LP-1227-01\Asus**.

**FIND(“\”,SUBSTITUTE(B5,”-“,”\”,LEN(B5)-LEN(SUBSTITUTE(B5,”-“,””))))**

Afterward, the **FIND** function returns the position of the Backslash (\) symbol in the string **LP-1227-01\Asus**. The output here is **11**.

**LEN(B5)-FIND(“\”,SUBSTITUTE(B5,”-“,”\”,LEN(B5)-LEN(SUBSTITUTE(B5,”-“,””))))**

Another subtraction operation is executed by this part of the formula to specify the string length of the brand name. Here, the output is **4**.

**RIGHT(B5,LEN(B5)-FIND(“\”,SUBSTITUTE(B5,”-“,”\”,LEN(B5)-LEN(SUBSTITUTE(B5,”-“,””)))))**

Finally, the **RIGHT** function returns the last 4 characters (i.e. the Product Brand name) from the string in **Cell B5**. The output here is **Asus**.

- Finally, use the
**Fill Handle**icon to copy the formula in the other cells of**Column C**.

**Read More: How to Use FIND Function in Google Sheets (5 Useful Examples)**

### 7. Joining SUBSTITUTE, LEN, and TRIM Functions to Count Words

The **SUBSTITUTE** function can also count the number of words in a string when it is joined with the **LEN** and **TRIM** functions. To demonstrate an example of this scenario, we require a new dataset like the following. Now let’s count the number of words in these strings.

__Steps:__

- To start, select
**Cell C5**first. - Afterward, type in the following formula-

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

- Next, press
**Enter**key to copy the formula into other cells.

__Formula Breakdown__

**TRIM(B5)**

First, the **TRIM** function removes all the leading, trailing, or repeated spaces if present in the string of **Cell B5**.

**LEN(TRIM(B5))**

Then, the first **LEN** function returns the trimmed length of the string in **Cell B5**.

**SUBSTITUTE(B5,” “,””))**

Next, the **SUBSTITUTE** function replaces all the spaces between the words in the string of **Cell B5**.

**LEN(SUBSTITUTE(B5,” “,””))**

After that, the second **LEN **function returns the length of the substituted string.

**LEN(TRIM(B5))-LEN(SUBSTITUTE(B5,” “,””))+1**

Finally, the subtraction of the two **TRIM** functions specifies the number of spaces present in the string in **Cell B5**. A value of **1** is added to that number to get the word count.

- Finally, use the
**Fill Handle**icon, to copy the formula in other cells of**Column C**.

**Read More:** **How to Remove Last Character from String in Google Sheets**

## Alternatives to SUBSTITUTE Function in Google Sheets

There are a few alternatives to the **SUBSTITUTE** function in Google Sheets. Here, we’ll discuss two such alternatives.

### 1. Utilizing Find and Replace Tool

We can achieve the output of the **SUBSTITUTE** function even without applying a formula by using the **Find and Replace** tool from the **Edit** ribbon in Google Sheets. But the limitation is it can replace only the duplicates of one string at a time.

__Steps:__

- First, select the range
**B5:B9**. - Afterward, use the keyboard shortcut
**CTRL+C**to copy the range.

- After that, select
**Cell C5**and then use the keyboard shortcut**CTRL+V**to paste the range.

- Now, select the range
**C5:C9**and then go to the**Edit**Select**Find and Replace**tool from the options.

- A window like the following will pop up. Then, in the
**Find**option, enter a Hyphen (-) symbol, and in the**Replace with**option, enter a Slash (/) symbol. - Now, click on the
**Replace All**option first and the**Done**option after that.

- This will make the desired changes to the range.

**Read More: How to Format Date with Formula in Google Sheets (3 Easy Ways)**

### 2. Applying REGEXREPLACE Function

**The REGEXREPLACE function** can also replace strings. However, unlike the **SUBSTITUTE** function, the **REGEXREPLACE** function can not replace particular instances.

__Steps:__

- Select
**Cell C5**first and then type in the following formula-

`=REGEXREPLACE(B5,$B$12,$C$12)`

- After that, press the
**Enter**key to get the desired changes.

- Finally, use the
**Fill Handle**icon to copy the formula in the other cells of**Column C**.

## How to Substitute Formula with Value in Google Sheets

Sometimes, we require to substitute a formula with value in our worksheets. For example, consider the following dataset where have used the **SUBSTITUTE** function to change strings. Now, let’s discuss a simple method that can substitute these formulas with values. No function can accomplish this, only the **Paste Special** command/shortcut can do this.

__Steps:__

- First, select the range
**C5:C9**. - Then, use the keyboard shortcut
**CTRL+C**to copy the range.

- Now, select
**Cell C5**and then use the keyboard shortcut**CTRL+SHIFT+V**to paste the range as a value. - Thus you can substitute a formula with value.

**Read More: ****Remove Everything after Character Using Google Sheets Formula**

## Things to Be Considered

- If we don’t enter the
**occurrence_number**argument, then the**SUBSTITUTE**function will replace every instance of the text to be replaced. - While substituting multiple characters, we must keep the
**IF function**in a set of parentheses.

## Conclusion

This concludes our article to learn how to use the **SUBSTITUTE** function in Google Sheets. I hope the demonstrated examples were sufficient for your requirements. Feel free to leave your thoughts on the article in the comment box. Visit our website **OfficeWheel.com** for more helpful articles.

**Related Articles**

**How to Use SEARCH Function in Google Sheets (5 Examples)****Remove Characters from a String in Google Sheets (6 Easy Examples)****Google Sheets Smart Fill: Recognize and Autocomplete Patterns (A Complete Guide)****How to Remove First Character in Google Sheets****Convert Text to Date in Google Sheets (3 Easy Ways)**