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:


Usage Example

The goal here is to return the text from cell A2 after replacing all values (defined in the D2:E8 table):

excel substitute array replacements

The following formula achieves this result:

excel function substitute array replacements

The formula has successfully performed all the replacements.

To better understand this formula, here is a slightly more readable version:


Now, let's break down this formula.

The formula starts with the REDUCE function with the initial value being the text from cell 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:


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


Finally, the SUBSTITUTE function will perform the replacement for the current row:


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:

excel custom function array substitute replacements

In this case, create a new custom function, enter SUBSTITUTE_ARRAY in the first field, and then the following formula in the last field:

excel custom function substitute array replacements
If needed, you can download the Excel file used here: replacements-array.xlsx
Note: requires Office 365.