Excel SUBSTITUTE Multiple Values: Formulas and Tips

To substitute multiple values in Excel, nest the SUBSTITUTE function for each old-to-new text pair, or use a replacement list with a dynamic-array formula in Microsoft 365. For example, with the text in A2, =SUBSTITUTE(SUBSTITUTE(A2,"cat","dog"),"red","blue") changes “red cat” to “blue dog.”

What SUBSTITUTE does

SUBSTITUTE changes specified text inside a text string. Its syntax is SUBSTITUTE(text, old_text, new_text, [instance_num]). The first three arguments are required: the original text or cell, the text to find, and the replacement text. The optional fourth argument limits the change to a particular occurrence.

For example, with a sentence in A2, =SUBSTITUTE(A2,"old","new") replaces every occurrence of “old” with “new.” With the same layout, =SUBSTITUTE(A2,"old","new",2) changes only the second occurrence. If the text appears just once, specifying instance 2 leaves it unchanged.

Unlike a broad search-and-replace operation, the function returns a result in a formula cell and leaves the source text untouched. This is useful when you want a repeatable transformation, need to preserve the original values, or expect the source data to change later.

Replace several values with nested formulas

For a small, fixed set of replacements, put one SUBSTITUTE inside another. Excel evaluates the innermost function first, then passes its result to the next function. With “red cat” in A2, this formula returns “blue dog”:

=SUBSTITUTE(SUBSTITUTE(A2,"cat","dog"),"red","blue")

The inner function changes “cat” to “dog,” producing “red dog.” The outer function then changes “red” to “blue.” The order matters because each later replacement operates on the text produced by earlier replacements.

For instance, with “red cat” in A2, =SUBSTITUTE(SUBSTITUTE(A2,"red","blue"),"cat","dog") also returns “blue dog.” But if the first rule changes “A” to “B” and the next changes “B” to “C,” original A characters become C as well. That cascading behavior may be intended, but it can cause unexpected results if replacement terms overlap.

Nested formulas are easy to audit when there are only two or three pairs. As the list grows, the formula becomes long and editing it is error-prone. Use a replacement table or Power Query when rules need regular maintenance.

Use a replacement table in Microsoft 365

A two-column table makes a larger set of rules easier to update. Put the source text in A2, old values in D2:D4, and their corresponding replacements in E2:E4. For example, D2:D4 might contain “NY,” “CA,” and “TX,” while E2:E4 contains “New York,” “California,” and “Texas.” In another cell, enter:

=REDUCE(A2,SEQUENCE(ROWS(D2:D4)),LAMBDA(txt,i,SUBSTITUTE(txt,INDEX(D2:D4,i),INDEX(E2:E4,i))))

If A2 contains “NY, CA, TX,” the result is “New York, California, Texas.” The formula walks through the rows of the mapping list in order, applying each pair to the text returned by the previous step. Keep each old value aligned with its replacement on the same row, and avoid blank old-value cells.

REDUCE, LAMBDA, and SEQUENCE require Microsoft 365 or Excel 2024. This approach is not available in older perpetual versions such as Excel 2019. In those versions, use nested SUBSTITUTE formulas, or consider Power Query for a repeatable multi-step process.

Choose the right method for the job

The best approach depends on whether you need a calculated result, a one-time edit, or a process that can be refreshed:

Nested SUBSTITUTE: Choose this for a few known text pairs and a formula result that updates when the source changes.
A mapping list with REDUCE: Choose this in Microsoft 365 or Excel 2024 when you have several rules that may be edited over time.
Find and Replace: Choose this for a one-time change to existing cell contents rather than a separate formula output.
Power Query: Consider it when you repeatedly import or clean data and want the transformations saved as refreshable steps.

These methods do not all behave identically. A formula preserves the original column unless you deliberately overwrite it with copied results. Find and Replace edits selected cells directly. Power Query creates a transformed query result, so changes to the source data can be handled by refreshing the query.

Use Find and Replace for a one-time edit

In Excel for Microsoft 365 on Windows, press Ctrl+H to open Find and Replace. Enter one old value in “Find what” and its replacement in “Replace with,” then select Replace or Replace All. To perform several distinct replacements, repeat the operation for each pair.

Before choosing Replace All, decide whether to search the current sheet or the whole workbook and whether to match entire cell contents. Selecting a range first limits the operation to that selection. Review the dialog’s options carefully: replacing a short string such as “CA” without whole-cell matching could also alter longer text containing those letters.

Find and Replace is not a single multi-pair mapping tool: each operation handles one find-and-replace pair. It can be faster than formulas for a small, one-off cleanup, but it changes the data in place. If the original values matter, duplicate the column or worksheet first.

Common problems and how to avoid them

Case sensitivity: SUBSTITUTE is case-sensitive. With “Blue, blue, BLUE” in A2, =SUBSTITUTE(A2,"blue","green") returns “Blue, green, BLUE.” It changes the exact lowercase match, not the other capitalization variants. Normalize case first if all forms should be treated alike.
Overlapping replacements: If a replacement creates text that matches a later old value, the later rule changes it again. Arrange the rules carefully and test a sample containing every relevant pattern.
Spaces and punctuation: “North-West” and “North West” are different strings. Check for leading spaces, trailing spaces, hyphens, and nonbreaking spaces if an apparently correct rule finds no match.
Replacing only one occurrence: Leaving out instance_num replaces every matching occurrence. Include the fourth argument only when you mean to target a particular occurrence.
Formula displayed instead of a result: Check that the cell is not formatted as Text and that Show Formulas is not enabled. After changing a text-formatted cell to General, re-enter the formula.

One important distinction is that SUBSTITUTE matches a specified text string, while Excel’s REPLACE function changes characters by position. They are not interchangeable: use SUBSTITUTE when you know the text to find, and REPLACE when you know where the characters are and how many to replace.

Check results before replacing source data

For formula-based cleanup, place the result in a neighboring column and inspect several examples, including values with repeated matches, mixed capitalization, and no match. If the output is correct and you need fixed text rather than formulas, copy the results and use Paste Special > Values to replace formulas with their current results. Keep a copy of the original data until you have checked the outcome.

For a mapping formula, check that every old value is unique enough for the intended job and that the list is ordered deliberately. A broad old value such as “A” can match letters inside ordinary words, not just a code standing alone. SUBSTITUTE does not understand word boundaries or distinguish a code from part of a longer phrase.

Frequently Asked Questions

Can Excel SUBSTITUTE replace more than one value at a time?
One SUBSTITUTE call handles one old-to-new pair. Nest calls for several fixed pairs, or in Microsoft 365 or Excel 2024 use a mapping list with REDUCE, LAMBDA, and SEQUENCE.

Does SUBSTITUTE replace every occurrence?
Yes. If you omit the optional instance_num argument, it replaces every matching occurrence of the specified text. Supply a number, such as 2, to replace only that occurrence.

Is SUBSTITUTE case-sensitive in Excel?
Yes. It matches capitalization exactly, so “cat” does not match “Cat.” If the capitalization varies, account for that in your rules or normalize the source text before applying replacements.

Can Find and Replace change multiple different values at once?
Not as a set of separate pairs in one operation. In Windows Excel, Ctrl+H opens the dialog for one find-and-replace pair; repeat it for each pair, or use a formula or Power Query for a reusable process.

Why does Excel show my formula instead of the substituted text?
The cell may be formatted as Text, or Show Formulas may be on. Set the cell to General and re-enter the formula, and check the Formulas tab for the Show Formulas setting.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.