Multiple Replacements with Excel
The SUBSTITUTE function allows replacing only one value with another.
Here's how you can perform multiple replacements with a single formula and, a bit further, how to create a custom function to make it even easier:
=SUBSTITUTE_ARRAY(text,replacement_array)
Usage Example
The goal here is to return the text from cell A2 after replacing all values (defined in the D2:E8 table):
The following formula achieves this result:
=REDUCE(A2,SEQUENCE(COUNTA(D2:D8)),LAMBDA(t,n,SUBSTITUTE(t,INDEX(D2:D8,n),INDEX(E2:E8,n))))
The formula has successfully performed all the replacements.
To better understand this formula, here is a slightly more readable version:
=REDUCE(A2,SEQUENCE(COUNTA(D2:D8)),LAMBDA(text,number,SUBSTITUTE(text,INDEX(D2:D8,number),INDEX(E2:E8,number))))Now, let's break down this formula.
The formula starts with the REDUCE function with the initial value being the text from cell A2:
=REDUCE(A2,The REDUCE function will then iterate through the array generated by the SEQUENCE function (which is a simple numbered array from 1 to 7 because the D2:D8 table contains 7 values) and take action on the text based on it:
SEQUENCE(COUNTA(D2:D8)),Therefore, the REDUCE function will execute the LAMBDA function 7 times, with arguments being the text to modify and the replacement number (from 1 to 7):
LAMBDA(text,number,Finally, the SUBSTITUTE function will perform the replacement for the current row:
SUBSTITUTE(text,INDEX(D2:D8,number),INDEX(E2:E8,number))))The values needed for replacements are retrieved by the INDEX function from the table based on the current number.
Custom Function
To simplify its usage, you can create a custom function and then use it very easily:
=SUBSTITUTE_ARRAY(A2,D2:E8)
In this case, create a new custom function, enter SUBSTITUTE_ARRAY in the first field, and then the following formula in the last field:
=LAMBDA(text,replacement_array,REDUCE(text,SEQUENCE(ROWS(replacement_array)),LAMBDA(t,n,SUBSTITUTE(t,INDEX(replacement_array,n,1),INDEX(replacement_array,n,2)))))