# 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)))))``