How to Substitute Multiple Values (Nested) in Excel

puneet-gogia-excel-champs

- Written by Puneet

In Excel, if you want to substitute multiple values from a single cell, in this case, you need to create a nested formula with the SUBSTITUTE function. And in this tutorial, we will learn to write this formula.

Write a Nested Substitute Formula to Replace Multiple Values

Below is the formula where we have used SUBSTITUTE functions five times to replace five values from cell A1.

replace-multiple-values
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"One","1"),"Two","2"),"Three","3"),"Four","4"),"Five","5")

This formula is a nested formula with the SUBSTITUTE function used five times to replace five values from the cell. And to make you understand this formula, I have structured it in the following way:

nested-formula-with-substitute-function

This formula replaces:

  • One ⇢ 1
  • Two ⇢ 2
  • Three ⇢ 3
  • Four ⇢ 4
  • Five ⇢ 5

As it’s a nested formula, the second function uses the value returned by the first, the third function uses the values returned by the second, and so on.

second-function-uses-the-returned-value-by-first

Nested Substitute with the Cell Reference

In the above formula, we have entered the values directly in the formula, one by one. But you can also create a cell reference for all the values.

nested-substitute-with-cell-reference
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,INDEX(D1:D5,1),INDEX(E1:E5,1)),INDEX(D1:D5,2),INDEX(E1:E5,2)),INDEX(D1:D5,3),INDEX(E1:E5,3)),INDEX(D1:D5,4),INDEX(E1:E5,4)),INDEX(D1:D5,5),INDEX(E1:E5,5))
two-index-functions-nested-in-formula

In each function of the formula, you have two INDEX functions. The first INDEX function returns the value you want to replace from the range E1:E5, and the second INDEX gets the new value to replace with from the range F1:F5.

Get the Excel File

Last Updated: December 25, 2023