Multiple Replacements with Google Sheets

With the SUBSTITUTE function, you can only replace one value with another value...

Here's how to perform multiple replacements with a single formula and (a little further down) how to create a named function to then make it even easier:

=SUBSTITUTE_TABLE(text, table)

Example of Use

The objective here is to return the text from A2 after having made all the replacements defined in the table D2:E8:

google sheets replacement table multiple replacements

This result can be obtained with the following formula:

google sheets function replacements table multiple

This formula has successfully made all the requested replacements.

For a better understanding, here is a more readable version of the same formula:


Now, let's decompose this formula.

The formula starts with the REDUCE function with the initial value being the text in A2:


The REDUCE function will then go through the array generated by the SEQUENCE function (which is a simple numbered array from 1 to 7, as the table D2:D8 contains 7 values) and perform an action on the text based on it:


Therefore, the REDUCE function will execute the LAMBDA function 7x here, whose arguments are: the text to be modified and the replacement number (from 1 to 7):


And finally, the SUBSTITUTE function will perform the replacement for the current line:


The values needed for the replacements are retrieved by the INDEX function in the table based on the current number.

Named Function

To simplify its use, you can create a named function to then use it very easily:

google sheets function substitute table multiple replacements

To do this, simply add a new named function, enter the 2 arguments text and table and then the following formula:


google sheets named function substitute table png multiple replacements

This formula is slightly different from the previous one (in order to obtain a simple function with 2 arguments).

If needed, you can copy the Google Sheets document (or view the document) with these examples.