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