How to Substitute Multiple Values in Google Sheets

Substituting multiple values in Google Sheets can be done using the formula SUBSTITUTE. The SUBSTITUTE function is used to replace existing text with new text in a string.

Here's how to use the SUBSTITUTE function in Google Sheets:

1. Open your Google Sheet with the data you want to substitute.
2. Click on a cell where you want the result to be displayed.
3. Type the following formula:

=SUBSTITUTE(text, search_for, replace_with, [occurrence])

• text: This is the cell or text string in which you want to substitute values.
• search_for: This is the text you want to find and replace.
• replace_with: This is the text you want to replace the search_for text with.
• [occurrence]: (Optional) The specific occurrence of the search_for text you want to replace. If omitted, all occurrences will be replaced.

To substitute multiple values, you can nest multiple SUBSTITUTE functions.

Here's an example:

Example

Let's say you have the following data in your Google Sheet:

A
1  I have a cat, a dog, and a fish.

And you want to substitute "cat" with "hamster", "dog" with "parrot", and "fish" with "turtle".

1. Click on cell B1 (or any other cell where you want the result to be displayed).
2. Type the following formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "cat", "hamster"), "dog", "parrot"), "fish", "turtle")

3. Press Enter.
4. The result in cell B1 will be:

I have a hamster, a parrot, and a turtle.

In this example, we nested three SUBSTITUTE functions to replace three different values in the original text.