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:

This result can be obtained with the following formula:

=REDUCE(A2,SEQUENCE(COUNTA(D2:D8)),LAMBDA(t,n,SUBSTITUTE(t,INDEX(D2:D8,n),INDEX(E2:E8,n))))

This formula has successfully made all the requested replacements.

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

=REDUCE(A2,SEQUENCE(COUNTA(D2:D8)),LAMBDA(text,number,SUBSTITUTE(text,INDEX(D2:D8,number),INDEX(E2:E8,number))))

Now, let's decompose this formula.

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

=REDUCE(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:

SEQUENCE(COUNTA(D2:D8));

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

LAMBDA(text,number,

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

SUBSTITUTE(text,INDEX(D2:D8,number),INDEX(E2:E8,number))))

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:

=SUBSTITUTE_TABLE(A2,D2:E8)

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

=REDUCE(text,SEQUENCE(ROWS(table)),LAMBDA(t,n,SUBSTITUTE(t,INDEX(table,n,1),INDEX(table,n,2))))

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