Excel SUBSTITUTE Function

SUBSTITUTE replaces one piece of text with another wherever it appears inside a string, and returns the updated text as a result. Unlike Find & Replace on the ribbon, it lives inside a formula, so the original cell stays untouched and the swap updates automatically if the source data changes. One thing to know upfront: SUBSTITUTE is case-sensitive. "Excel" and "excel" are not the same match, and Excel won't warn you when it skips one.

Syntax

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Parameter	Required	Description
text	Yes	The text you're searching within. Usually a cell reference, but you can type a literal string in quotes.
old_text	Yes	The exact text you want to find and replace. Matching is case-sensitive and doesn't support wildcards.
new_text	Yes	The text to insert in place of old_text. Use an empty string "" if you want to delete the matched text instead of replacing it.
instance_num	No	Which occurrence of old_text to replace, counting from the left. Leave it out and SUBSTITUTE replaces every occurrence it finds.

Basic Example

You're cleaning a vendor list where some records use the abbreviation "Inc" and others need the full "Incorporated" before a mail merge goes out.

=SUBSTITUTE(A2, "Inc", "Incorporated")

// A2             = the vendor name to check, e.g. "Acme Supply Inc"
// "Inc"          = the exact text to find
// "Incorporated" = the text that replaces it

SUBSTITUTE scans A2 for the text "Inc," finds it at the end of the string, and swaps it for "Incorporated." No instance_num was given, so if "Inc" appeared twice in the same cell, both would get replaced. The formula returns "Acme Supply Incorporated."

How SUBSTITUTE Works

It replaces every match unless you tell it not to

By default, SUBSTITUTE swaps every instance of old_text it finds in the string. =SUBSTITUTE("2024-01-15, 2024-02-20", "2024", "2025") returns "2025-01-15, 2025-02-20." Both years change. If you only want one, that's what instance_num is for.

Matching is case-sensitive

=SUBSTITUTE("Excel Report", "excel", "Google Sheets") returns "Excel Report" unchanged, because "excel" (lowercase) doesn't match "Excel" (capitalized) in the source text. This trips up more people than any other part of the function. There's no case-insensitive option built in.

instance_num targets one specific occurrence, not a range

Say a phone log has entries formatted as "555-100-2000-9" and you only need to change the second dash to a period. =SUBSTITUTE(A2, "-", ".", 2) returns "555-100.2000-9." Only the second dash changed. The first and third stayed as dashes.

An empty new_text deletes the matched text

Set the third argument to "" and SUBSTITUTE removes the match instead of replacing it. =SUBSTITUTE("Item #4521*", "*", "") returns "Item #4521." This is the standard way to strip a specific character out of a string without touching anything else.

Common Use Cases

Removing currency formatting so numbers calculate correctly

Sales figures were pasted in as text, like "$1,200.50," and formulas that reference the cell return an error instead of a number.

=SUBSTITUTE(SUBSTITUTE(A2, "$", ""), ",", "") + 0

// inner SUBSTITUTE   = removes the dollar sign
// outer SUBSTITUTE   = removes the thousands comma
// + 0                = forces the leftover text into a number

The nested SUBSTITUTE calls strip out both non-numeric characters, and the +0 at the end converts the result from text to an actual number Excel can sum or average.

Swapping a delimiter for a line break

A notes column stores order contents as "Apples, Bananas, Cherries" and you need each item stacked on its own line inside the same cell.

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

CHAR(10) inserts a line break wherever the comma-and-space combination appears. Turn on Wrap Text for the cell, or the line breaks won't display even though they're there.

Replacing only the third occurrence in a repeated pattern

Product SKUs follow the format "REG-2024-001-A," and a batch of them needs the third segment updated without touching the year or the region code.

=SUBSTITUTE(A2, "-", "/", 3)   // changes only the third dash to a slash

Counting starts from the left. In "REG-2024-001-A" the third dash sits between "001" and "A," so only that one gets swapped.

Handling Errors

SUBSTITUTE doesn't throw errors often, but it will return #VALUE! if the arguments don't line up with what it expects.

Common causes of #VALUE!:

• instance_num is zero, negative, or text instead of a whole number
• text points to a multi-cell range instead of a single cell
• The referenced cell already contains an inherited error from another formula

=IFERROR(SUBSTITUTE(A2, "Inc", "Incorporated"), "Check source cell")

Notes & Gotchas

Why does SUBSTITUTE skip text I know is there?

The most common cause is a case mismatch. SUBSTITUTE treats "Report" and "report" as different strings, so if your data has inconsistent capitalization, some matches get skipped with no warning. Run UPPER or LOWER on both the source text and old_text first if case shouldn't matter.

Does SUBSTITUTE support wildcards like * or ??

No. SUBSTITUTE only matches literal text, so "inv*" looks for that exact three-character-plus-asterisk string, not "any word starting with inv." If you need pattern matching, REGEXREPLACE (Excel 365) handles wildcards and regex patterns; SUBSTITUTE doesn't.

What happens if old_text never appears in the string?

SUBSTITUTE returns the original text unchanged, and no error is thrown. This makes it safe to run across a whole column even if only some rows contain the text you're targeting.

Why does swapping delimiters leave extra spaces behind?

If your original text uses ", " (comma plus space) as a separator and you substitute only the comma, the space gets left stranded next to your new delimiter. The fix is a second nested SUBSTITUTE that targets the space directly, or substitute the full ", " sequence in one pass instead of just the comma.

Can SUBSTITUTE work on numbers, not just text?

Yes, but the result comes back as text, even if the original cell held a number. If you need to run math on the output, add +0 or wrap the formula in VALUE() to convert it back.

Related Functions

Function	Use this when...
REPLACE	You know the exact starting position and character count to replace, rather than matching by text.
FIND	You need the numeric position of text inside a string instead of replacing it.
TRIM	You need to remove extra spaces, including the ones SUBSTITUTE can leave behind after a delimiter swap.