# How to Substitute Multiple Values (Nested) in Excel

- 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.

`=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.

## Get the Excel File

Last Updated: December 25, 2023