Excel: How to Substitute Multiple Text Values

Formulas & functions · Updated November 19, 2026

In Excel, substitute multiple text strings by nesting SUBSTITUTE functions, one for each change. If A2 contains “The blue chair is blue,” this formula changes both instances of “blue” and also changes “chair”: =SUBSTITUTE(SUBSTITUTE(A2,"blue","green"),"chair","sofa")

How the SUBSTITUTE function works

SUBSTITUTE searches text for a specified sequence of characters and replaces it with another sequence. Its syntax is SUBSTITUTE(text, old_text, new_text, [instance_num]). The first three arguments are required; the optional fourth argument limits the change to a particular occurrence. The function returns text, so it can be used inside other formulas or in a helper column without changing the original cell.

A single SUBSTITUTE call handles one old-to-new text pair. To apply several pairs to the same cell, put one call inside another. Excel evaluates the innermost call first, then passes its result to the outer call. With the A2 example above, the inner call changes “blue” to “green,” producing “The green chair is green.” The outer call then changes “chair” to “sofa,” so the final result is “The green sofa is green.”

For a basic replacement, assume A2 contains “Order pending” and the goal is to change “pending” to “shipped.” Use =SUBSTITUTE(A2,"pending","shipped"). The result is “Order shipped.” This basic form is also the building block for multiple replacements.

Build a formula for several replacements

For a small, fixed number of changes, nest one function for each pair. The replacement order matters because each outer call receives the text produced by the inner calls. With A2 containing “The blue chair is blue,” the formula in the opening example first changes all instances of “blue” to “green,” then changes “chair” to “sofa.” The result is “The green sofa is green.”

A replacement can introduce text that a later step also searches for. That can be useful, but it can also cause unexpected changes. For example, changing “cat” to “dog” and then changing “dog” to “fox” means that original “cat” text becomes “fox” too. If the order is reversed, original “dog” text may also become “fox.” Plan the sequence and test it on representative values.

For a longer chain, line breaks can make a formula easier to read in the formula bar. The following example assumes A2 contains “North Branch – pending,” and replaces three strings: “North” with “West,” “Branch” with “Office,” and “pending” with “open.” Enter it in B2 and fill down as needed:

With the source text in A2, use =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"North","West"),"Branch","Office"),"pending","open"). The result is “West Office – open.”

When a formula becomes difficult to maintain, split the work into helper columns: use one column for the first replacement and the next column for the second. This makes each step visible and easier to troubleshoot. It does use more worksheet space, but it is often clearer than a deeply nested formula.

Replace all occurrences or just one

By default, SUBSTITUTE replaces every occurrence of the old text in the input. That is why the two instances of “blue” in the opening example both change. To replace only one occurrence, supply the optional fourth argument. For example, if A2 contains “red, red, red,” the formula below changes only the second “red.”

With “red, red, red” in A2, use =SUBSTITUTE(A2,"red","blue",2). The result is “red, blue, red.” The instance number counts occurrences from left to right. If the specified occurrence is not present, the original text is returned unchanged.

Be careful when nesting instance-specific replacements. The instance number applies to the text passed into that particular call, which may already have been changed by an inner call. If the goal is to update one specific occurrence among several different search terms, use separate steps or inspect a few sample results before filling the formula down a large range.

Use Find and Replace for a one-time edit

If the changes are a one-off cleanup and you want to modify the cells themselves rather than produce formula results, use Excel’s Find and Replace command. On Windows, select the cells to edit, then choose Home > Find & Select > Replace, or press Ctrl+H. Enter the text to locate in “Find what” and the replacement in “Replace with.” Choose Replace All to update every match, or Find Next and Replace to review matches individually.

Find and Replace processes one search term at a time. For several different changes, run it once for each pair, and consider the order for the same reason that order matters in nested formulas. If the workbook contains multiple sheets, review the search options before replacing: Excel can search the current sheet or the workbook, and the selected scope determines how broad the edit will be.

On Excel for Mac, use Edit > Find > Replace; the exact keyboard shortcut can depend on the Mac keyboard setup. In Excel for the web, the available controls and dialogs may differ slightly from the desktop app, so verify the selected range and scope before using Replace All. Find and Replace is convenient, but it changes source cells directly and may be harder to audit than a formula in a separate column.

Choose between a formula and Find and Replace

The best method depends on whether the source data should remain intact and whether the same rules will be reused. A formula keeps the original value available and updates its result when the source changes. Find and Replace edits the selected cells immediately, which is usually faster for a one-time correction but less reversible once other workbook edits have followed.

  • Use nested SUBSTITUTE for a few known text changes, especially when you want to keep source values and create a cleaned result column.
  • Use Find and Replace for a one-time edit to a carefully selected range when overwriting the original values is intended.
  • Use Power Query for repeatable cleanup of imported data or for a longer list of replacement rules that should be applied during a refresh.

Before a bulk edit, save the workbook or make a copy. For formulas, fill the formula down a small sample and check the outputs before applying it to thousands of rows. For Find and Replace, select only the intended cells and confirm whether Excel is searching the sheet or the whole workbook.

Use Power Query for repeatable cleanup

Power Query is useful when the same transformation must be repeated as new data arrives, such as cleaning a monthly CSV export. In Microsoft 365 for Windows, start by selecting the data and choosing Data > From Table/Range. In the Power Query Editor, select the relevant column and choose Transform > Replace Values. Enter the value to find and its replacement, then repeat the command for each additional pair. Choose Home > Close & Load to return the transformed data to Excel.

Each replacement becomes a recorded query step, which makes the process repeatable when the source data is refreshed. The original source can remain unchanged while the query produces cleaned output. This approach is usually more manageable than a formula with many nested calls when there are numerous rules or a recurring import process. It is not a direct edit of the original cells: the results are loaded as query output, and changes to the source data are reflected when the query is refreshed.

Power Query’s menus can differ somewhat by platform and update channel. Excel for Mac has Power Query capabilities in Microsoft 365, but not every connector or editing feature is identical to Windows. If a particular command is missing, check the current Excel version and platform support rather than assuming the query steps will be exactly the same.

Common issues and limitations

SUBSTITUTE looks for exact text and is case-sensitive. Replacing “Blue” does not automatically replace “blue.” It also does not interpret asterisks or question marks as wildcard characters; it treats them as literal characters to search for. If case-insensitive matching, patterns, or more flexible transformations are needed, a simple nested SUBSTITUTE formula may not fit the requirement.

Another common surprise is that a match can occur inside a longer word. Replacing “art” with “design” also changes “artist” to “designist,” because the function searches for the character sequence wherever it occurs. To change whole words only, use a method that can recognize word boundaries or check the surrounding characters; ordinary SUBSTITUTE does not have a whole-word-only option.

Also consider spaces and punctuation. A value that looks like “pending” may actually contain a trailing space, a nonbreaking space from a web export, or different punctuation. If the formula returns the original text, inspect the source carefully. For data imported from other systems, cleaning invisible characters may be necessary before the replacement works as expected.

Finally, replacing text in a number can affect how Excel treats the result. For example, a cell containing a numeric value may display a format such as a date or currency, while a formula that substitutes characters returns text. If the result needs to be calculated as a number afterward, convert or parse it deliberately and verify the outcome rather than assuming it remains numeric.

Frequently Asked Questions

Can Excel SUBSTITUTE replace several words in one formula?
Yes. Nest one SUBSTITUTE function for each old-and-new text pair. Excel applies the innermost replacement first, so arrange the calls in an order that will not unintentionally alter later matches.

Does SUBSTITUTE replace every occurrence?
Yes, unless you provide the optional instance_num argument. Without that argument, all occurrences of the specified text are replaced. With it, only the matching occurrence at that position is changed.

Is SUBSTITUTE case-sensitive?
Yes. “West,” “WEST,” and “west” are different search strings for SUBSTITUTE. If capitalization varies in the source, use a different approach or normalize the text before applying exact replacements.

Can I use wildcards with SUBSTITUTE?
No. Asterisks and question marks are treated as literal characters by SUBSTITUTE, not as wildcard patterns. Excel’s Find and Replace supports wildcard searches when the relevant option is enabled, but that is a separate tool and behaves differently from a formula.

How do I replace many values from a list?
For a short list, nest one SUBSTITUTE call per pair. For a long or recurring list, Power Query’s Replace Values steps are often easier to review and refresh. Excel does not provide a simple multiple-pair argument for a single SUBSTITUTE call.

How do I remove text instead of replacing it with another word?
Set new_text to an empty string, written as two quotation marks with nothing between them. For example, with the text in A2, =SUBSTITUTE(A2,"-","") removes every hyphen and leaves the other characters in place.

Related

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.