SUBSTITUTE In Google Sheets

What Is SUBSTITUTE Function In Google Sheets?

SUBSTITUTE function in Google Sheets, as the name suggests, helps users substitute the value in the cell or cell range with different values. It is a default function available in both Excel and Google sheets. Remember, using SUBSTITUTE function in Google sheets, we can substitute the enter data or replace a specific occurrence also. With the help of LEFT, MID, FIND functions, we can also replace multiple values.

For example, consider the below table showing data in column A and result in column B, respectively.

Now, we need to replace the text string, ‘Apple’ with ‘Pineapple’ in cell B2. To begin with, select the cell B2 and insert the SUBSTITUTE function formula in Google sheets, =SUBSTITUTE(A2,”A”,”Pine”) and then, press Enter key.

We will be able to see the substituted result in Google sheets, as shown in the below image.

In this article, let us learn how to use the SUBSTITUTE function in Google sheets with detailed examples.

Syntax

The formula of SUBSTITUTE function is =SUBSTITUTE(text_to_search,search_for,replace_with,[occurrence_number])

where,

• text_to_search is the cell or cell range having the text string where we want to replace a content
• search_for is the text string that is used to search and replace with the original text
• replace_with is the new text string which we want to replace and substitute in the search_for string
• occurrence_number is the optional argument that shows the number of times we want to replace the text string with new values.

Except occurrence_number, all the arguments are mandatory in SUBSTITUTE function in Google sheets function.

How To Use SUBSTITUTE Function In Google Sheets?

We can use SUBSTITUTE function in Google sheets in two different methods. They are:

• Select the SUBSTITUTE function under the Insert tab
• Manually enter the SUBSTITUTE function

Method #1 – Select the SUBSTITUTE Function under the Insert Tab

The steps to use the SUBSTITUTE function under the Insert tab are:

Step 1: To begin with, we need to insert the cell range in the Google spreadsheets. Next, we need to select the cell where we want to find the result.

Step 2: Next, select the Insert tab. Click on the Function option.

Step 3: Now, click on the Text category. Scroll down to select the SUBSTITUTE function.

Step 4: We will be able to see the SUBSTITUTE function in Google sheets. Now, insert the arguments and the value to be replaced.

Step 5: Press Enter key.

We will be able to see the result in the active cell, in Google sheets.

Likewise, we can use the SUBSTITUTE function in Google sheets under the Insert tab.

Method #2 – Manually Enter the SUBSTITUTE Function

The steps to use the SUBSTITUTE function manually are:

Step 1: To begin with, we need to insert the cell range in the Google spreadsheets. Next, we need to select the cell where we want to find the result.

Step 2: Next, enter the =SUB function. Then, click on the SUBSTITUTE function.

Step 3: Alternatively, we can also type =SUBSTITUTE function directly.

Step 4: We will be able to see the SUBSTITUTE function in Google sheets. Now, insert the arguments and the value to be replaced.

Step 5: Press Enter key.

We will be able to see the result in the active cell, in Google sheets.

Likewise, we can use the SUBSTITUTE function in Google sheets manually.

Examples

Now, let us learn how to use the SUBSTITUTE function in Google sheets with the help of the following examples.

Example #1 – Replace Specific Occurrence With SUBSTITUTE

For example, consider the below table showing data and result in columns A and B, respectively.

Now, let us learn how to replace specific occurrence with SUBSTITUTE function in Google sheets.

The steps are:

Step 1: To begin with, we need to insert the cell range in the Google spreadsheets. In this example, we need to select the cell range A1:B3. Next, we need to select the cell where we want to find the result. In this example, we need to select the cell B2.

Step 2: Now, we need to substitute the letter, “C” with “H” to replace the word “Cat” with “Hat”. So, enter the SUBSTITUTE function in Google sheets.

So, the complete formula is =SUBSTITUTE(A2,”C”,”H”)

Step 3: Press Enter key.

We will be able to see the result in the active cell B2, as shown in the below image.

Similarly, to replace the word “Cat” with “Mat”, we need to substitute the letter, “C” with “M”. So, the formula is =SUBSTITUTE(A3,”C”,”M”)

Press Enter key.

We will be able to see the result in cell B3, as shown in the below image.

Likewise, we can replace specific occurrence with SUBSTITUTE function.

Example #2 – Use SUBSTITUTE With LEFT, MID, FIND

For example, consider the below table showing data and result in columns A and B, respectively.

Now, let us learn how to use the SUBSTITUTE function in Google sheets with LEFT, MID and FIND Google sheets functions.

The steps are:

Step 1: To begin with, we need to insert the cell range in the Google spreadsheets. In this example, we need to select the cell range A1:B3. Next, we need to select the cell where we want to find the result. In this example, we need to select the cell B2.

Step 2: Now, we need to find the text string from the LEFT using the SUBSTITUTE function in Google sheets.

So, the complete formula is =LEFT(SUBSTITUTE(A2,”/”,”,”),5)

Step 3: Press Enter key.

We will be able to see the result in the active cell B2, as shown in the below image.

Similarly, using the AutoFill option, we can find the result in the cell B3, as shown in the below image.

Note that the formula we used is same since the text we want to extract is ‘South’ with 5 characters just like ‘North’ in cell A2.

Likewise, we can use the SUBSTITUTE function with LEFT, MID and FIND Google sheets functions.

Example #3 – Replace Multiple Values With SUBSTITUTE

For example, consider the below table showing data and result in columns A and B, respectively.

Now, let us learn how to use the SUBSTITUTE function in Google to replace multiple values.

The steps are:

Step 1: To begin with, we need to insert the cell range in the Google spreadsheets. In this example, we need to select the cell range A1:B6. Next, we need to select the cell where we want to find the result. In this example, we need to select the cell B2.

Step 2: Now, we need to substitute the “/” with a comma “,”.

So, the complete formula is =SUBSTITUTE(A2,”/”,”,”)

Step 3: Press Enter key.

We will be able to see the result in the active cell B2, as shown in the below image.

Similarly, using the AutoFill option, we can find the result in the cell range B3:B6, as shown in the below image.

Likewise, we can use the SUBSTITUTE function in Google sheets to replace multiple values.

Example #4 – Nested SUBSTITUTE Functions

For example, consider the below table showing data and result in columns A and B, respectively.

Now, let us learn how to use the nested SUBSTITUTE functions.

The steps are:

Step 1: To begin with, we need to insert the cell range in the Google spreadsheets. In this example, we need to select the cell range A1:B5. Next, we need to select the cell where we want to find the result. In this example, we need to select the cell B2.

Step 2: In the data, the text string C1 stands for Customer, N stands for North and S stands for Selected. Now, we need to substitute the short forms into detailed text string. We can so this using nested SUBSTITUTE functions in Google sheets.

So, the complete formula is =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,”C”,”Customer”),”N”,”North”),”S”,”Selected”)

Step 3: Press Enter key.

We will be able to see the result in the active cell B2, as shown in the below image.

Similarly, using the AutoFill option, we can find the result in the cell range B3:B5, as shown in the below image.

Likewise, we can use nested SUBSTITUTE functions in Google sheets.

Important Things To Note

• SUBSTITUTE function in Google sheets helps users substitute the data in the given text string.
• Some of the other functions in Google sheets that we can use instead of SUBSTITUTE function is REPLACE function.
• Remember, we need to mention the text string we want to replace with using double quotations.

Frequently Asked Questions

Recommended Articles

Guide to What Is SUBSTITUTE In Google Sheets. We learn how to use SUBSTITUTE function In Google Sheets, formula, examples, working template. You can learn more from the following articles.