Excel SUBSTITUTE Function Not Working? Fixes That Help

If Excel’s SUBSTITUTE function is not working, check that the text you want to replace matches exactly, that the formula is entered as a formula, and that the optional occurrence number is valid. SUBSTITUTE replaces matching text inside a value; it is case-sensitive, does not use wildcards, and returns text rather than changing the original cell.

What the SUBSTITUTE function does

SUBSTITUTE is useful when you need to replace a particular piece of text wherever it appears in a cell, or replace only a selected occurrence. Its syntax is SUBSTITUTE(text, old_text, new_text, [instance_num]). The brackets indicate that instance_num is optional: leave it out to replace every matching occurrence, or provide a positive occurrence number to replace only that one.

For example, suppose A2 contains the text Invoice-2026-04. With that value in A2, enter this in B2 to change every hyphen to a slash; the result is Invoice/2026/04.

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

The function does not edit A2. It creates a result in the formula cell, so the source value remains available. This is useful for cleaning or standardizing imported data without overwriting the original.

Check the formula and occurrence number

A frequent cause of an unexpected result is leaving out, misplacing, or misunderstanding the fourth argument. If it is omitted, all matches are replaced. If it is 2, only the second match is replaced. For A2 containing Invoice-2026-04, this formula in B2 returns Invoice-2026/04:

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

The occurrence number counts matches from left to right. It does not refer to a character position. If the requested occurrence is not present, Excel has no matching occurrence to change, so the original text is returned. An instance_num below 1 produces a #VALUE! error.

Check the argument order, too: the old text comes before the replacement text. If you reverse them, Excel may search for the replacement string instead of the text you intended to change. Commas are the usual argument separators in Microsoft 365 for Windows, but some regional settings require semicolons. If Excel rejects a correctly structured formula, check the list separator used by your installation.

Look for exact-text mismatches

SUBSTITUTE matches literal text, not an approximate pattern. A normal space is different from two spaces, a tab, or a nonbreaking space. A hyphen-minus is also different from an en dash or em dash. These differences are hard to see in imported descriptions, copied web content, and addresses, but they prevent a match.

For example, if A2 contains the text A B with two ordinary spaces, this formula in B2 removes every ordinary space and returns AB:

=SUBSTITUTE(A2," ","")

If A2 instead contains a nonbreaking space, that formula may appear to do nothing. A nonbreaking space is commonly represented by character code 160 in Excel. With the source text in A2, this formula replaces those characters with ordinary spaces:

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

Replacing the character with a regular space can make the result easier to clean further, but it does not remove regular spaces. Inspect a few source cells, or compare character counts, before assuming the text is identical. SUBSTITUTE is case-sensitive as well: searching for cat does not match Cat. It also treats a question mark or asterisk as an ordinary character, not as a wildcard.

Fix formulas displayed as text or not recalculating

If a cell displays the formula itself instead of its result, Excel may be treating the entry as text. A leading apostrophe can also mark an entry as text. Select the cell, change its number format to General using Home > Number Format, then re-enter the formula. One way is to press F2 and then Enter on Windows; on Mac, double-click the cell to edit it and press Return.

Also check whether Show Formulas is enabled, because that view displays formulas in cells rather than their calculated results. Toggle it from Formulas > Show Formulas, or use Ctrl+` on Windows. Shortcut behavior can vary with keyboard layout; the ribbon command is the clearer check. Excel for the web has a similar Show Formulas option on the Formulas tab.

If the formula is correct but its result is stale, calculation may be set to Manual. In desktop Excel, select Formulas > Calculation Options > Automatic. Excel for Mac has calculation options on the Formulas tab too. After changing the setting, recalculate the workbook if needed. A workbook set to manual calculation can leave a valid SUBSTITUTE result unchanged until recalculation occurs.

Understand spaces, punctuation, and repeated replacements

SUBSTITUTE replaces every exact match by default, even when matches appear next to one another. To remove a particular punctuation mark, use an empty replacement string. For example, with A2 containing 555-0148, this formula in B2 returns 5550148:

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

When you need to remove more than one different character, nest SUBSTITUTE functions. With A2 containing 555-0148 (555), this formula in B2 removes hyphens, spaces, and parentheses, returning 5550148555:

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

Nested formulas can become difficult to maintain. If the list of characters is long, consider a helper column for each transformation or use a more suitable cleaning method. For a small, fixed set of literal characters, nesting is straightforward and works in older Excel versions as well as Microsoft 365.

When the result looks right but is the wrong data type

SUBSTITUTE returns text. That matters when replacing separators in values that look numeric: a result such as 1,250 may be text, so arithmetic or sorting can behave differently than expected. The visual appearance alone does not confirm a cell’s data type. If the result must be numeric, convert it deliberately and check that the input format matches your Excel region’s number conventions.

For example, suppose A2 contains the text 1,250, where the comma is a thousands separator. With that layout, this formula in B2 removes the comma and converts the result to a number; the result is 1250:

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

This example assumes the value uses a comma as a thousands separator and no decimal portion. In other regional formats, commas and periods may have different meanings, so verify the source data before converting. If the cell contains other words or characters that VALUE cannot interpret as a number, conversion will fail until those characters are handled.

Choose SUBSTITUTE, REPLACE, or Find and Replace

SUBSTITUTE and REPLACE solve different problems. SUBSTITUTE searches for specified text and changes matching occurrences; REPLACE changes a set number of characters starting at a position you specify. For example, changing a known three-character prefix at the start of every code is positional work, while changing every occurrence of a hyphen is text matching. Use the function that describes the transformation you intend.

For a one-time edit to actual cell contents, Excel’s Find and Replace may be simpler than a formula. Press Ctrl+H on Windows to open Replace, enter the search and replacement text, and choose whether to replace one match or all matches. On Mac, use Edit > Find > Replace or the Find controls available in the Excel version. Review the selected range and replacement options before choosing Replace All, since it changes the cells directly. A formula is preferable when the transformation should update automatically or the original data must remain intact.

TRIM and CLEAN are not interchangeable with SUBSTITUTE. TRIM removes excess ordinary spaces at the beginning and end and reduces repeated ordinary spaces between words; CLEAN removes certain nonprinting characters. Neither is a general replacement for searching for a particular string. For imported data containing unusual whitespace, combining a targeted SUBSTITUTE with the appropriate cleanup function may be necessary.

A quick troubleshooting checklist

Confirm the first argument points to the cell that contains the text, and that old_text and new_text are in the correct order.
Check capitalization, spaces, punctuation, and lookalike characters in both the source and the search text.
Remove instance_num to replace all matches, or verify that the requested occurrence exists and is greater than zero.
Make sure the formula cell is not formatted as Text and that the entry does not begin with an apostrophe.
Check that Show Formulas is off and calculation is set to Automatic if results appear missing or out of date.
Remember that the result is text; convert it if a later calculation needs a number.

Frequently Asked Questions

Why does SUBSTITUTE not replace text with different capitalization?
SUBSTITUTE is case-sensitive. The old_text argument must match the case in the source. If case should not matter, normalize the text and search term to the same case in a separate step, or use a different approach suited to the task.

Can SUBSTITUTE use wildcards such as * or ?
No. SUBSTITUTE searches for literal characters, so an asterisk or question mark in old_text is treated as that character, not a wildcard. Use Find and Replace or another method when pattern-based matching is required.

Why does SUBSTITUTE return the original text?
Usually, Excel cannot find an exact match, the requested occurrence is not present, or the characters look similar but differ, such as an ordinary space versus a nonbreaking space. Check the source text and the case and punctuation in old_text.

Does SUBSTITUTE change the original cell?
No. It returns the modified text in the cell containing the formula. The original value stays unchanged unless you deliberately copy the result and paste it over the source as values.

Why is the result not usable in a calculation?
SUBSTITUTE returns text, even when the result looks like a number. Convert it to a numeric value only after removing unwanted characters, and confirm that separators follow the number format your Excel installation expects.

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.