SUBSTITUTE in Excel: Syntax, Examples and Errors
Updated 2026-10-13 · Free · Works in Excel & Google Sheets
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])Quick answer
SUBSTITUTE swaps a piece of text for another, everywhere it appears: =SUBSTITUTE(A2, "-", " ") turns "555-01-2345" into "555 01 2345". It is case-sensitive, replaces all matches unless instance_num pins one, and instance_num of 0 returns #VALUE!.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])| Argument | What it means |
|---|---|
| text | The cell or literal to search through. Required. |
| old_text | The exact piece of text to replace. Matching is case-sensitive and literal — no wildcards. Required. |
| new_text | What replaces it. An empty string "" deletes the match outright. Required. |
| [instance_num] | Which occurrence to replace: 3 changes only the third match. Omit it and every occurrence changes. Must be 1 or higher. |
Depending on your regional settings, arguments are separated by semicolons instead of commas: =SUBSTITUTE(A2; "-"; " "). This is standard in countries where the comma is the decimal separator.
Two behaviors surprise everyone once: matching is case-sensitive ("ID" and "id" are different targets), and without instance_num the replacement hits every occurrence — there is no replace-first-only switch.
Basic examples
=SUBSTITUTE(A2, "-", " ")Dashes to spaces: phone numbers, part codes and ID numbers written with hyphens become readable in one shot. Every dash changes — the formula repeats the swap through the whole cell.
=SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", "")Deleting characters: the new_text "" erases each match without a trace. Nesting two SUBSTITUTEs strips both parentheses from "(555) 0102" in one formula — one layer per character you want gone.
=SUBSTITUTE(A2, "/", "-", 2)The instance_num argument pins a single occurrence: only the second slash flips to a dash, the first and third stay put. Occurrences count left to right from the start of the text.
=LEN(A2)-LEN(SUBSTITUTE(A2, " ", ""))+1The classic word count: deleting all spaces and comparing the two lengths yields the number of spaces, and +1 turns that into the number of words. No VBA, no Data Analysis — one formula.
The result of SUBSTITUTE is text — even when it looks like a number. "1234" built from a cleaned "1 234" will not sum, average or chart. Wrap it: =VALUE(SUBSTITUTE(A2, " ", "")) converts the cleaned text back into a real number.
Common errors and how to fix them
| Symptom | Most common cause | Fix |
|---|---|---|
| Nothing gets replaced | Case mismatch — "ID" will not match "id" — or an invisible trailing space makes "abc " different from "abc". | Match the exact case, clean the source with TRIM first, or use the Find & Replace dialog, where case matching is a checkbox instead of a hard rule. |
| #VALUE! | instance_num is 0 or negative. | Use 1 or higher — or drop the argument entirely to replace all occurrences. |
| The wrong occurrence changed | Occurrences count left to right, and instance 2 hit the match you meant to keep. | Count occurrences first with the LEN trick, or make the target unique by adding context: =SUBSTITUTE(A2, "/2024/", "/2025/") hits the slash that sits inside that pattern only. |
| More changed than expected | Short old_text matches inside longer words: replacing "1" also hits "12" and "21". | Include the surrounding characters in old_text, or pin the occurrence with instance_num. |
| Numbers stop calculating | The result is text, even when it looks numeric. | Wrap the whole thing in VALUE, or keep the original cell for calculations. |
One more silent no-op: when instance_num exceeds the number of actual occurrences, SUBSTITUTE returns the original text unchanged — no error, no hint. If a formula "does nothing", count the matches before assuming the spelling is wrong.
SUBSTITUTE vs. REPLACE
| SUBSTITUTE | REPLACE | |
|---|---|---|
| Finds by | The text itself, anywhere in the cell | A fixed position: start position + number of characters |
| Use when | You know what to replace, not where it sits | You know the exact position and length |
| Scope | Every occurrence — or one pinned via instance_num | One fixed block per call |
| Example | =SUBSTITUTE(A2, "-", " ") | =REPLACE(A2, 4, 2, "XX") |
REPLACE swaps characters at fixed positions — the right tool when the layout is rigid, like "always overwrite characters 4–5". The moment the target text can sit anywhere in the cell, SUBSTITUTE takes over, because it hunts the text instead of the position.
SUBSTITUTE vs. Find & Replace
| SUBSTITUTE | Find & Replace (Ctrl+H) | |
|---|---|---|
| Lives in | A formula — the result sits in its own cell and the source stays untouched | A dialog — it edits the cells in place, overwriting the originals |
| Recalculates | Yes — change the source and the replacement follows | No — one shot; new data needs a new run |
| Repeats across | Fill-down turns one formula into thousands | The whole workbook in one pass |
| Case handling | Always case-sensitive | Case-insensitive by default, with a Match case checkbox |
The practical split: Find & Replace for a one-time cleanup of whatever is already in the sheet, SUBSTITUTE when the cleanup must survive new data — imports, refreshes, copy-paste storms. Many sheets end up with a SUBSTITUTE column feeding off a raw import column.
SUBSTITUTE in Google Sheets
Same name, same four arguments, same case-sensitive literal matching — the formulas above paste over unchanged. Sheets wraps the same idea in a power tool: REGEXREPLACE accepts regular expressions, so one formula can normalize a whole mess of formats at once.
- Comma-decimal locales switch to semicolons: =SUBSTITUTE(A2; "-"; " ").
- The Find and Replace dialog (Ctrl+H) in Sheets adds a Search using regular expressions option Excel does not have.
- For deleting characters, "" as new_text behaves exactly like Excel — no trace left.
Limitations and pro tips
- One old_text per call: cleaning several different characters means nesting — each layer strips one more character class.
- Order matters when patterns overlap: replace "::" before ":", or the double colon is already gone by the time the single-colon rule runs.
- No wildcards — old_text is matched literally, so "*" and "?" hunt literal asterisks and question marks. Pattern power lives in Find & Replace or, in Sheets, REGEXREPLACE.
- Line breaks are CHAR(10): =SUBSTITUTE(A2, CHAR(10), " ") flattens a wrapped cell into one line — turn the result into real numbers afterwards if needed.
- A repeatable cleanup pipeline: SUBSTITUTE the noise, TRIM the residue, VALUE the numbers — three columns that survive every re-import.
Treat SUBSTITUTE as the immune system of an imported sheet: raw data lands dirty, the substitute columns clean it on arrival, and every formula downstream feeds off the clean version — nobody ever edits the raw column by hand again.
Related Tools
Related Functions
Frequently Asked Questions
What does SUBSTITUTE do in Excel?
It replaces a piece of text with another, everywhere it appears in a cell: =SUBSTITUTE(A2, "-", " ") swaps every dash for a space. With the optional instance_num argument it can change just one specific occurrence instead.
Is SUBSTITUTE case-sensitive?
Yes — old_text must match the case exactly, and there is no ignore-case switch. For case-insensitive one-time cleanups use the Find & Replace dialog, which offers a Match case checkbox instead of enforcing the rule.
How do I replace only the second occurrence?
Pass 2 as the fourth argument: =SUBSTITUTE(A2, "/", "-", 2). Occurrences count left to right; every other match stays untouched. Any number from 1 up works, and 0 or a negative returns #VALUE!.
How do I remove all spaces from a cell?
Replace them with nothing: =SUBSTITUTE(A2, " ", ""). The empty new_text deletes every space in the cell. For number-looking results, wrap in VALUE afterwards so the cleaned text calculates again.
What is the difference between SUBSTITUTE and REPLACE?
SUBSTITUTE hunts text wherever it sits; REPLACE overwrites a fixed position and length (=REPLACE(A2, 4, 2, "XX")). Use SUBSTITUTE when you know what to change, REPLACE when you know exactly where it is.
Why does SUBSTITUTE return #VALUE!?
The fourth argument — instance_num — is 0 or negative. It must be 1 or higher, or omitted entirely to replace every occurrence. Exceeding the actual occurrence count is not an error: the text just comes back unchanged.
Does SUBSTITUTE work in Google Sheets?
Yes — same name, same arguments, same case-sensitive literal matching. Sheets adds REGEXREPLACE for pattern-based replacements and a regex-capable Find and Replace dialog.
Ready to try it on your own file?
Open Find and Replace in Excel — It's Free