To replace more than one value in a cell, nest SUBSTITUTE inside itself — each function works on the result of the one before it: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"One","1"),"Two","2"),"Three","3"). Build it from the inside out, so the innermost SUBSTITUTE always holds the original cell reference.
This works up to about five or six replacements, after which the formula gets hard to read and edit. In Excel 365 you have two shorter routes.
If every value is being replaced with the same thing, use REGEXREPLACE with the pipe | as an “or”: =REGEXREPLACE(A1,"One|Two|Three","") strips all three in one go. If each value needs a different replacement, keep the old and new values in two columns and use REDUCE: =REDUCE(A1,SEQUENCE(ROWS(D1:D10)),LAMBDA(a,i,SUBSTITUTE(a,INDEX(D1:D10,i),INDEX(E1:E10,i)))) — that handles any number of pairs and updates when you add a row.
Note SUBSTITUTE is always case-sensitive, while REGEXREPLACE is case-sensitive by default but can be switched off with its fifth argument.
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.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.