You can use the following formula to substitute multiple values in a cell in Excel:
This particular formula makes the following substitutions in the text in cell A1:
- oldtext1 is replaced with newtext1
- oldtext2 is replaced with newtext2
Note that in this example we substitute two values in a cell, but you can create as many nested SUBSTITUTE functions as you’d like to substitute as many values as you’d like.
The following example shows how to use this formula in practice.
Example: Substitute Multiple Values in Cell in Excel
Suppose we have the following dataset in Excel that contains information about various basketball players:
We can use the following formula to substitute “Guard” with “Gd.” and “Forward” with “Fd.” for each value in the Position column:
We’ll type this formula into cell C2 and then click and drag it down to each remaining cell in column C:
Notice that the following substitutions were made in each cell in column A:
- The string “Guard” was replaced with “Gd.”
- The string “Forward” was replaced with Fd.”
Note: If none of the strings in the formula are found in the text that you search, then the formula will simply return the original text without any substitutions.
The following tutorials explain how to perform other common tasks in Excel: