SUBSTITUTE Formula Not Working in Excel? Fix It

If Excel’s SUBSTITUTE formula is not working, first check that the text to change and the text to find match exactly, including spaces and capitalization, and that the formula begins with an equals sign. In Microsoft 365 for Windows, the basic form is =SUBSTITUTE(A2,"old","new"); it replaces every occurrence of “old” in A2 with “new.”

When the result still looks wrong, the cause is often a hidden character, a formula stored as text, an unexpected match count, or an output that is text rather than a number. The checks below help identify which issue applies before you rebuild the formula.

What SUBSTITUTE does—and what it does not do

SUBSTITUTE searches within a text value and returns a new text value with matching text replaced. It does not change the original cell. If the source is A2, the result appears in the formula cell, while A2 remains unchanged. This makes it useful for cleaning imported labels, standardizing product names, and removing punctuation from text.

With the text “North, North” in A2, this formula returns “South, South” because it replaces all matching occurrences:

With the text in A2, enter this formula in B2:

=SUBSTITUTE(A2,"North","South")

SUBSTITUTE is case-sensitive: “North” and “NORTH” are different strings for matching purposes. It also looks for exact text, not a pattern. It does not treat an asterisk or question mark as a wildcard. If you need to replace text based on a pattern, or search without regard to case, a different method may be more suitable.

Verify the formula and its arguments

The usual causes of a formula error are a missing equals sign, an unmatched quotation mark or parenthesis, or arguments in the wrong order. The first argument is the text to edit; the second is the exact text to find; the third is what to put in its place. An optional fourth argument limits replacement to a particular occurrence. Excel inserts argument separators based on regional settings. Most English-language installations use commas, but some use semicolons. If Excel rejects a correctly structured formula, check whether your installation expects semicolons between arguments.

For a targeted replacement, assume A2 contains “North, North.” Enter this formula in B2 to replace only the second occurrence:

=SUBSTITUTE(A2,"North","South",2)

The result is “North, South.” The occurrence number must be a positive whole number. A zero or negative number returns #VALUE!. If the optional argument is omitted, Excel replaces every occurrence. If it is set to 1, only the first matching occurrence is changed.

Check that text values are enclosed in straight double quotation marks, as shown in the examples. Curly “smart quotes” copied from a document are not the same characters and can make a formula invalid. If you are unsure, click the formula bar and retype the quotation marks and parentheses directly in Excel.

Check capitalization, matching, and occurrence count

Because matching is case-sensitive, a formula searching for “apple” will not replace “Apple” or “APPLE.” For example, with “apple APPLE” in A2, this formula in B2 returns “pear APPLE”:

=SUBSTITUTE(A2,"apple","pear")

If the text varies in capitalization, one option is to normalize its case before replacing. That changes the case of the entire source text, however, so it may not suit names or labels where capitalization matters. Another option is to use Find and Replace for a one-time edit and check the available matching options. Find and Replace can be case-insensitive depending on its settings; SUBSTITUTE itself remains case-sensitive.

Also confirm how many matches the formula should change. Omitting the fourth argument replaces every exact occurrence, which may be more than intended. Including an occurrence number changes just that match, counting from left to right. If a cell contains repeated text, inspect the source value in the formula bar rather than relying only on how the cell looks.

Diagnose formula display and calculation problems

If the cell displays the formula characters instead of a result, Excel may be treating the entry as text. Check that the cell does not have Text number format and that there is no apostrophe before the equals sign. Select the cell, choose Home > Number Format > General, then press F2 and Enter to make Excel interpret the entry again. On Mac, use the same number-format menu and press Control+U to edit the selected cell; pressing Return confirms the edit.

If formulas appear throughout the worksheet instead of calculated values, Show Formulas may be turned on. On Windows, press Ctrl+` (the grave-accent key, usually beside 1) to toggle it, or choose Formulas > Show Formulas. On Mac, use Formulas > Show Formulas if the keyboard shortcut differs on your keyboard.

When the formula is correct but the result does not update after changing the source cell, check calculation mode. In Excel for Windows, choose Formulas > Calculation Options > Automatic. In Excel for Mac, look under Formulas for Calculation Options. In Automatic mode, dependent formulas normally recalculate when their inputs change. If other formulas are also stale, the workbook may be set to Manual calculation; changing that setting can recalculate formulas across the workbook.

A different displayed result can also be caused by cell formatting. A formula may return text that looks like a date or number, but formatting does not convert that text into a numeric value. Check the formula bar and test the output in a new cell before assuming SUBSTITUTE failed.

Look for invisible spaces and imported characters

Two strings that look identical on screen may contain different characters. A regular space, a nonbreaking space copied from a webpage, and a tab are not interchangeable. SUBSTITUTE only replaces the exact character sequence requested. This is a common reason a formula works for some rows but not for others in data copied from websites, PDFs, or external systems.

To remove ordinary hyphens and spaces from a value in A2, enter this formula in B2. If A2 contains “AB- 123,” the result is “AB123”:

=SUBSTITUTE(SUBSTITUTE(A2,"-","")," ","")

Nested SUBSTITUTE calls apply from the inside out: the inner formula removes hyphens, and the outer formula removes regular spaces from that result. Be cautious about removing spaces from names or descriptions, since the result may become harder to read or match.

A nonbreaking space is often represented by character code 160 in Windows Excel. If A2 contains “North” followed by a nonbreaking space and then “Shore,” this formula in B2 replaces that character with an ordinary space:

=SUBSTITUTE(A2,CHAR(160)," ")

If you also need to remove leading and trailing ordinary spaces and collapse repeated ordinary spaces to one, use TRIM around the replacement. With the same A2 layout, enter this formula in B2:

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

TRIM handles regular spaces; it does not remove every possible invisible character. If a stubborn value remains unmatched, inspect the source, test a cleaned copy, or use Excel’s CLEAN function for certain nonprinting characters. CLEAN does not remove every Unicode character, so imported data may need more specific cleanup.

Understand number results and error messages

SUBSTITUTE returns text, even when the result contains only digits. That matters when the next calculation expects a number. For example, suppose A2 contains the text “1,250” and the comma is a thousands separator. This formula in B2 removes the comma and returns the text “1250”:

=SUBSTITUTE(A2,",","")

If you need a numeric result for arithmetic, wrap the replacement in VALUE. With the same text “1,250” in A2, enter this formula in B2; it returns the number 1250:

=VALUE(SUBSTITUTE(A2,",",""))

The example assumes a comma is a thousands separator in the source value. Number conventions vary by region, so confirm whether commas and periods represent thousands or decimals in your data before converting. VALUE can also fail if the cleaned text is not a number in the format Excel expects.

Common errors provide clues. #VALUE! can result from an invalid occurrence number, or it may be inherited from an error in the referenced cell. #NAME? often points to a misspelled function name or text that should have been placed in quotation marks. A formula that returns the original text unchanged may simply be searching for a string that is absent or spelled differently. Check the source value, spaces, capitalization, and punctuation before adding more functions.

Choose SUBSTITUTE, Find and Replace, or another function

Use SUBSTITUTE when the replacement should update automatically as source cells change, or when each row needs the same repeatable cleanup. Fill the formula down a column to apply it to other rows, and keep the original data intact until you have checked the output.

Use Find and Replace for a one-time change to existing cell contents. On Windows, press Ctrl+H; on Mac, press Control+H. Review the options in the dialog before replacing, especially if you want to search within formulas or match case. Unlike a formula, a completed Find and Replace operation edits the selected data, so consider making a copy first when the change is broad.

For other needs, select a function based on the kind of match:

Use REPLACE when you know the character position and number of characters to change, rather than searching for matching text.
Use SEARCH when you need to locate text without case-sensitive matching, often as part of a larger formula.
Use FIND when you need a case-sensitive position search.
Use Power Query for repeatable cleanup of imported tables when several transformations are required. In Microsoft 365 for Windows, access it from Data > Get & Transform Data; available commands can vary by data source.

These tools solve different problems. Replacing a known string is not the same as removing text at a fixed position, matching a pattern, or cleaning an entire data-import process.

Frequently Asked Questions

Why does SUBSTITUTE only replace some of the text?
It replaces exact matches. Differences in capitalization, spaces, punctuation, or invisible characters can leave some occurrences untouched. Also check whether the optional occurrence argument limits the formula to one match.

Is SUBSTITUTE case-sensitive in Excel?
Yes. “Red” and “red” are different matches. If the source uses inconsistent capitalization, normalize the text first or choose a method that fits your case-insensitive search requirement.

Why does SUBSTITUTE return text instead of a number?
SUBSTITUTE produces text. If the result must be used in arithmetic, convert it with VALUE after checking that the resulting characters form a valid number in your regional format.

Can SUBSTITUTE replace a wildcard such as an asterisk?
SUBSTITUTE treats the asterisk as a literal character; it does not use wildcards. If the goal is to replace an actual asterisk, put it in quotation marks as the old text. For pattern-based replacement, use a suitable Find and Replace option or another data-cleaning approach.

How do I replace only the first occurrence?
Use the optional fourth argument set to 1. The occurrence count starts at the first match from the left. Leave that argument out when every exact occurrence should be replaced.

Why does my formula show as text?
Check for Text number format, a leading apostrophe, or Show Formulas mode. Set the cell to General and re-enter the formula, and turn off Show Formulas if the worksheet is displaying formulas instead of results.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.