The Excel SUBSTITUTE function, explained interactively.
SUBSTITUTE replaces one text fragment with another inside a string. Unlike REPLACE, it matches by value, not position — so “strip every dash” is one argument, not a position calculation. The instance_num switch lets you target the first, second, or Nth occurrence when replace-all is too aggressive.
How to use SUBSTITUTE
- Type
=SUBSTITUTE(and point at the text you want to modify — usually a cell reference. - Add a comma and old_text — the fragment to find. Quoted literals are fine (
"-"); cell references work too. - Add another comma and new_text — what to write in its place. Pass
""to delete every match. - Optionally add instance_num to replace only the Nth occurrence. Omit it (or pair with replace every match in the demo) to affect all matches at once.
Click Scenario ▾ in the demo to flip between replacing every dash, only the first dash, only the second dash, and other patterns — the formula bar rewrites itself for each.
old_text.old_text is case-sensitive. Pass "" as new_text to delete every match.1 = first match, 2 = second, etc. Omit to replace every occurrence — the default, and the most common use.| A | B | |
|---|---|---|
| 1 | Source | Replaced |
| 2 | +1-415-555-2041 | +14155552041 |
| 3 | A-01-2045 | A012045 |
| 4 | 2026-04-19 | 20260419 |
| 5 | low-cost low-energy option | lowcost lowenergy option |
SUBSTITUTE syntax and arguments
Three required arguments plus an optional Nth-instance selector. See Microsoft’s official SUBSTITUTE reference for the canonical specification.
"" (empty) is invalid and returns the source unchanged.old_text. "" deletes the match entirely. Can be longer or shorter than old_text — the string grows or shrinks.1 = first, 2 = second, etc. Omit to replaceevery occurrence (default and most common).0 or negative values return #VALUE!.SUBSTITUTE examples
Four patterns that cover nearly every SUBSTITUTE you’ll ever write.
Example 1: SUBSTITUTE to strip dashes from phone numbers
The canonical cleanup case — remove every occurrence of a character.
+1-415-555-2041 becomes +14155552041. Omitting instance_num means every dash disappears. Pair with LEFT / RIGHT to isolate the area code before cleaning the rest.
Example 2: SUBSTITUTE with instance_num
Target only the Nth occurrence when replace-all goes too far.
Replaces only the first dash. A-01-2045 becomes A/01-2045 — the remaining dashes survive. Useful when the first separator carries different semantics (say, country code) than later ones.
Example 3: Chained SUBSTITUTE for multiple cleanups
Nest calls to perform several replacements in one formula.
First strips dashes, then converts spaces to underscores. Readable up to 3 nests; past that, split into a helper column. Modern alternative in Excel 365: chain with TEXTSPLIT / TEXTJOIN for more complex transforms.
Example 4: SUBSTITUTE to count occurrences
A clever SUBSTITUTE idiom — compare lengths before and after a replace to count how many matches existed.
Each removed dash shortens the string by 1, so the difference equals the dash count. Generalise with / LEN(old) when old_text is longer than one character.
Common SUBSTITUTE errors and fixes
Four failure modes, each with what to check and how to recover.
SUBSTITUTE ignoring matches you can see
Cause: case mismatch. SUBSTITUTE is case-sensitive, so "low" won’t match Low or LOW.
Either preprocess with LOWER or UPPER, or nest two SUBSTITUTE calls to cover both cases. Modern alternative: REGEXREPLACE (Google Sheets) handles case-insensitivity via a flag.
SUBSTITUTE returns #VALUE!
Cause: instance_num is 0, negative, or a non-numeric value.
Pass a positive integer or omit the argument entirely. Computed instance numbers should be guarded with MAX(1, ...).
SUBSTITUTE output looks identical to the source
Cause: old_text doesn’t actually appear in the source — often a whitespace / unicode mismatch (non-breaking space vs regular space, straight vs smart quotes).
Compare lengths: if LEN(A2) = LEN(SUBSTITUTE(A2, ...)), no match was made. Use CODE() to inspect the exact character codes of the source.
SUBSTITUTE turning numbers into text
Cause: SUBSTITUTE always returns text — even if you’re cleaning a numeric string, the result won’t add.
Wrap with VALUE(): =VALUE(SUBSTITUTE(A2, ",", "")) coerces a comma-stripped number string back to a real number.
SUBSTITUTE vs REPLACE, TRIM & CLEAN
Four text-cleanup tools. Pick based on whether you know what to replace, where to replace, or you’re targeting specific whitespace or non-printables.
| Function | Matches by | Args | Typical use |
|---|---|---|---|
| SUBSTITUTE | Value — the text fragment you name | text, old, new, [instance] | “Strip every dash”, “replace first space with underscore”. |
| REPLACE | Position — start index + length | text, start, num_chars, new | “Replace characters 4-6” — good for fixed formats. |
| TRIM | (nothing to specify) | text | Strip leading/trailing spaces and collapse internal runs to one space. |
| CLEAN | (nothing to specify) | text | Remove non-printable ASCII (characters 0-31) — useful on imports from legacy systems. |
Rule of thumb: SUBSTITUTE for value-based replacements, REPLACE for fixed-position fixes, TRIM for whitespace, CLEAN for invisible control characters. Modern Excel 365 and Google Sheets also ship REGEXREPLACE, which subsumes all four for anyone comfortable writing patterns.
SUBSTITUTE frequently asked questions
6.01What’s the difference between SUBSTITUTE and REPLACE?▸
SUBSTITUTE replaces by matching text (“find every dash and remove it”). REPLACE replaces by position (“replace 4 characters starting at position 5”). Use SUBSTITUTE when you know what to look for; use REPLACE when you know where the target sits.
6.02How do I replace only the first occurrence with SUBSTITUTE?▸
Pass 1 as the instance_num argument: =SUBSTITUTE(A2, "-", "", 1). Only the first dash is removed; every other dash in the string stays. Instance 2 targets the second match, and so on.
6.03Is SUBSTITUTE case-sensitive?▸
Yes. SUBSTITUTE matches exactly, so "low" and "LOW" are different. For a case-insensitive replacement, preprocess the source with LOWER or UPPER, or nest two SUBSTITUTE calls to cover both cases.
6.04How do I remove all spaces from a cell?▸
Use =SUBSTITUTE(A2, " ", ""). This strips every space — useful for cleaning phone numbers, SKUs, and IDs. TRIM handles leading/trailing spaces and internal doubles only; SUBSTITUTE with an empty replacement is the nuclear option.
6.05Can I chain several SUBSTITUTE calls?▸
Yes — nest them. =SUBSTITUTE(SUBSTITUTE(A2, "-", ""), " ", "_") first strips dashes, then swaps spaces for underscores. Readable up to 3 nests; past that, consider a helper column or the newer TEXTSPLIT / TEXTJOIN chain.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.