SUBSTITUTE Function in Excel — Find & Replace by Formula (2026 Guide) | Sheets & Cells
Text · Cleanup Trio

SUBSTITUTE Function in Excel

Replaces specific text within a string. The formula version of Find & Replace — but smarter, because you can target only the 2nd or 5th occurrence, nest it for multi-character cleanup, and use it inside other formulas. Also the only fix for CHAR(160) non-breaking spaces.

Universal Support
SUBSTITUTE has existed in Excel since Excel 1.0 (1985). Works identically in Excel 2003, 2007, 2010, 2013, 2016, 2019, 2021, Microsoft 365, Excel Online, Excel Mobile, Google Sheets, and LibreOffice Calc. No compatibility caveats.

⚠ SUBSTITUTE vs Ctrl+H — The Case-Sensitivity Trap

You've used Find & Replace a thousand times. SUBSTITUTE looks like the formula version, but behaves in one crucial way that will bite you.

Ctrl+H (Find & Replace dialog)

  • Case-insensitive by default
  • Optional "Match case" checkbox
  • Optional "Match entire cell contents"
  • One-time operation, not a formula
  • Modifies cells in place

=SUBSTITUTE(text, old, new)

  • Always case-sensitive — no switch
  • No entire-cell match option
  • Live formula, updates when source changes
  • Can be nested inside other formulas
  • Result written to a new cell, source untouched
The bug this causes: =SUBSTITUTE("I love Excel and excel and EXCEL", "Excel", "Sheets") returns "I love Sheets and excel and EXCEL" — only the exact-case match. To make it case-insensitive, normalize first: =SUBSTITUTE(LOWER(A2), "excel", "sheets"). Or use SEARCH+REPLACE (case-insensitive alternative) if position-based replacement is fine.

Quick Answer

SUBSTITUTE replaces occurrences of one text string with another inside a larger string. Case-sensitive. With an optional 4th argument, it targets only the Nth occurrence instead of all matches.

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

Example: =SUBSTITUTE("555-234-5678", "-", "") returns "5552345678". With Nth: =SUBSTITUTE("a-b-c-d", "-", "|", 2) returns "a-b|c-d" — only the second dash gets replaced.

📊 Text Cleanup Trio Practice Workbook

All 5 SUBSTITUTE examples plus TRIM and LEN patterns on 20 rows of realistic messy CRM data.

↓ Download .xlsx (Free)
Filetext-cleanup-trio-2026.xlsx
Size~22 KB
Sheets6 tabs
CoversTRIM · LEN · SUBSTITUTE
1985
Introduced
3–4
Arguments
100%
Compat
70+
Templates Use It

Syntax

SUBSTITUTE takes 3 required arguments and 1 optional argument for surgical control.

ArgumentTypeDescription
text Required The source text — a string, cell reference, or formula returning text.
old_text Required The text to find. Case-sensitive. An empty string ("") won't do anything.
new_text Required The text to insert in place of old_text. Empty string ("") effectively deletes old_text.
instance_num Optional The occurrence number to target. Omit to replace all occurrences. Set to 2 to replace only the 2nd match, 5 for the 5th, etc.

Case matters. SUBSTITUTE treats "Excel", "excel", and "EXCEL" as three different strings. This is different from Ctrl+H Find & Replace which defaults to case-insensitive.

5 Worked Examples

Example 01

Remove all dashes from a phone number

The bread-and-butter use — strip a character out of a string entirely by replacing it with "".

=SUBSTITUTE("555-234-5678", "-", "")

Returns: "5552345678" — clean digits only, ready for a phone-format validator or database load.

Example 02

Nested SUBSTITUTE — digits-only phone cleanup

The real-world phone-cleanup pattern. Users type phones in every conceivable format: parens, dashes, dots, spaces. Nest SUBSTITUTE calls to strip them all in one formula.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")",""),"-","")," ",""),".","")
Raw Phone InputCleaned Output
"(555) 123-4567""5551234567"
"555.234.5678""5552345678"
"555 345 6789""5553456789"
"(555)456-7890""5554567890"

Pair with =LEN(cleaned) = 10 to validate the result is a proper US 10-digit number. Together they form a complete phone-cleanup pipeline.

Example 03

The case-sensitivity trap — how to work around it

SUBSTITUTE respects case exactly. That's often what you want. When it isn't, wrap the source in LOWER or UPPER first.

FormulaResult
=SUBSTITUTE("Love Excel and excel and EXCEL", "Excel", "Sheets")"Love Sheets and excel and EXCEL"
=SUBSTITUTE(LOWER("Love Excel and excel and EXCEL"), "excel", "sheets")"love sheets and sheets and sheets"

Approach 1 preserves original casing but only matches exact case. Approach 2 catches every variant but loses the original casing. Pick the trade-off your data needs.

Example 04

Nth-occurrence targeting — the 4th argument nobody knows

This is SUBSTITUTE's superpower. The optional 4th argument lets you replace only one specific match, not all of them. Ctrl+H can't do this at all.

FormulaWhat it doesResult
=SUBSTITUTE("a-b-c-d-e", "-", "|")Replace all dashes"a|b|c|d|e"
=SUBSTITUTE("a-b-c-d-e", "-", "|", 1)Only the 1st dash"a|b-c-d-e"
=SUBSTITUTE("a-b-c-d-e", "-", "|", 2)Only the 2nd dash"a-b|c-d-e"
=SUBSTITUTE("a-b-c-d-e", "-", "|", 4)Only the 4th dash"a-b-c-d|e"

Real use: convert only the last "." in a filename to something else, split a path at a specific separator, or fix one specific instance of a repeated word without touching the others.

Example 05

Fix CHAR(160) — the only way to handle non-breaking spaces

Copy-paste from Word, web pages, or PDF often injects CHAR(160) — a non-breaking space that TRIM ignores. SUBSTITUTE is the only reliable fix.

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

SUBSTITUTE converts every CHAR(160) into a regular space, then TRIM handles the rest. This exact formula is the industry-standard cleanup — bookmark it. If your data source is truly unpredictable, expand to =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))) to also strip line breaks and other non-printing characters.

Interactive Playground

Source
A2 → "555-234-5678"
A3 → "Excel is great excel"
A4 → "one-two-three-four"
A5 → "one-two-three-four"
Formula & Result
=SUBSTITUTE(A2, "-", "")
"5552345678"
=SUBSTITUTE(A3, "excel", "Sheets")
"Excel is great Sheets"

Only the lowercase match got replaced — case-sensitive.

=SUBSTITUTE(A4, "-", "|")
"one|two|three|four"
=SUBSTITUTE(A5, "-", "|", 2)
"one-two|three-four"

Only the 2nd occurrence.

The Text Cleanup Trio

Three functions that work together to turn messy imports into production-clean data.

SUBSTITUTE vs REPLACE — Which One To Reach For

Excel's most-confused pair. Both replace text, but they answer completely different questions.

AspectSUBSTITUTEREPLACE
Finds by Content — you name the text Position — you name character N to M
Syntax SUBSTITUTE(text, old, new, [n]) REPLACE(text, start, num_chars, new)
Use when You know what to look for ("remove all dashes") You know where to change ("replace chars 5–7")
Example =SUBSTITUTE("555-234", "-", "/") → "555/234" =REPLACE("555-234", 4, 1, "/") → "555/234"
Case-sensitive? Yes (always) N/A (position-based, no matching)
Can target Nth occurrence? Yes — 4th argument No
Common use Cleanup, normalization, character-swap Masking (SSN → XXX-XX-1234), fixed-position edits

Rule of thumb: if you can describe the change in words like "replace this specific text" → SUBSTITUTE. If you'd describe it like "change characters 5 through 8" → REPLACE.

The Nested SUBSTITUTE Pattern — How to Keep It Readable

Nesting 5 SUBSTITUTEs is powerful but reads like a wall of parentheses. Two techniques to keep it maintainable.

Technique 1: Formula on multiple lines (Alt+Enter inside the formula bar)

=SUBSTITUTE(
  SUBSTITUTE(
    SUBSTITUTE(
      SUBSTITUTE(
        SUBSTITUTE(A2, "(", ""),
      ")", ""),
    "-", ""),
  " ", ""),
".", "")
// each SUBSTITUTE strips one character; result is digits-only

Technique 2: LAMBDA + LET (Excel 365 modern approach)

=LET(
  src, A2,
  noParens, SUBSTITUTE(SUBSTITUTE(src, "(", ""), ")", ""),
  noDashes, SUBSTITUTE(noParens, "-", ""),
  noSpaces, SUBSTITUTE(noDashes, " ", ""),
  noDots, SUBSTITUTE(noSpaces, ".", ""),
  noDots
)
// each step is named — debuggable, testable, no wall of parens

Both produce identical results. Technique 1 works in every Excel version. Technique 2 needs Excel 365 or 2021+ but is dramatically easier to read a month later — the "future-you" test.

Common Errors

SymptomCauseFix
Nothing changed Case mismatch — old_text is "Excel" but source has "EXCEL". Or trailing space in old_text that's not in source. Normalize case: =SUBSTITUTE(LOWER(A2), "excel", "sheets"). Or trim first: =SUBSTITUTE(TRIM(A2), old, new).
Too much changed The old_text pattern also appears inside other words. E.g. replacing "in" catches "in", "into", "inside", "raining". Add surrounding characters to old_text: replace " in " (with spaces) instead of "in". Or use Nth-occurrence targeting.
#VALUE! instance_num is 0 or negative. Or one of the arguments is an error value. instance_num must be a positive integer (1, 2, 3, ...). Omit it entirely to replace all matches. Wrap with IFERROR for defensive formulas.
Only 1st match replaced You wanted to replace all matches but accidentally passed instance_num=1. Remove the 4th argument entirely. =SUBSTITUTE(A2, "-", "") without the 4th arg replaces every match.
CHAR(160) still won't budge You're substituting a regular space when the source has non-breaking spaces. They look identical but aren't. Explicitly reference CHAR(160): =SUBSTITUTE(A2, CHAR(160), " "). Then TRIM the result.
Numbers become text SUBSTITUTE always returns text. If the source was a number, the result loses numeric behavior. Wrap with VALUE: =VALUE(SUBSTITUTE(A2, ",", "")). Or double-negative: =--SUBSTITUTE(A2, ",", "").

📊 Practice on Real Messy Data

Every SUBSTITUTE pattern — nested phone cleanup, case-sensitivity fix, Nth-occurrence, CHAR(160) — on 20 realistic CRM rows.

↓ Get Workbook

Related Functions

Compatibility

PlatformSupportedVersionNotes
Excel for Windows✓1.0+ (1985)All versions
Excel for Mac✓1.0+All versions
Microsoft 365✓CurrentFull support
Excel Online✓CurrentFull support
Excel Mobile (iOS/Android)✓CurrentFull support
Google Sheets✓AllIdentical behavior
LibreOffice Calc✓AllIdentical behavior

How to Use SUBSTITUTE — Step by Step

  1. Click an empty cell next to the text you want to modify.
  2. Type =SUBSTITUTE( — Excel auto-suggests as soon as you type "SUB".
  3. Reference the source cell, then add the text to find and the text to replace it with, both in quotes: =SUBSTITUTE(A2, "-", "").
  4. Close and press Enter. The result appears in your new cell. The source cell is untouched.
  5. To target only one occurrence, add a 4th argument: =SUBSTITUTE(A2, "-", "/", 2) replaces only the second dash. Ctrl+H can't do this at all — this is SUBSTITUTE's signature power.
  6. For case-insensitive matching, wrap the source in LOWER or UPPER first: =SUBSTITUTE(LOWER(A2), "excel", "sheets"). The result comes back all-lowercase; if that's not what you want, consider REPLACE combined with SEARCH instead.

Explore More Functions

📊 One More Time — Grab the Workbook

Every SUBSTITUTE pattern plus TRIM and LEN. Free, one file, six sheets, ready to open.

↓ Download Free

Frequently Asked Questions

Is SUBSTITUTE case-sensitive?

Yes, always. "Excel", "excel", and "EXCEL" are three different strings to SUBSTITUTE. This is different from Ctrl+H Find & Replace which is case-insensitive by default.

To make SUBSTITUTE work like a case-insensitive replace, wrap the source in LOWER or UPPER first: =SUBSTITUTE(LOWER(A2), "excel", "sheets"). Trade-off: the result comes back in the normalized case, so you lose the original casing.

What's the difference between SUBSTITUTE and REPLACE?

SUBSTITUTE finds by content ("replace all dashes"). REPLACE works by position ("replace characters 5 through 8"). They solve different problems even though both change text.

Use SUBSTITUTE for cleanup, normalization, and character-swap tasks. Use REPLACE for masking (like showing only the last 4 digits of an SSN) or when you know the exact position where the change should happen.

How do I replace only the last occurrence of a character?

Count how many occurrences exist first, then target that number: =SUBSTITUTE(A2, "-", "|", LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))). The inner LEN-diff pattern counts dashes; the outer SUBSTITUTE targets the last one.

Great for splitting a filename at the last dot (extension), or targeting the final delimiter in a path when you don't know how many segments there are.

Can SUBSTITUTE replace multiple different characters at once?

Not in a single call — each SUBSTITUTE handles one find-and-replace pair. But you can nest them: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-",""),"(",""),")","") strips dashes, then parens, then closing parens.

For readability with 3+ nested SUBSTITUTEs, use LET in Excel 365: named intermediate steps make the intent obvious and debuggable. See the Nested Pattern section above.

Why is SUBSTITUTE not replacing anything?

Two common causes. First, case mismatch — "Excel" won't match "EXCEL". Test by copying the exact string from the source cell into your formula. Second, invisible character differences: the source might have a trailing space or CHAR(160) that isn't in your old_text argument.

Diagnostic: =LEN(A2) to see the actual character count. If it's higher than expected, run =TRIM(SUBSTITUTE(A2, CHAR(160), " ")) before your intended SUBSTITUTE.

Does the empty string ("") work as old_text?

Yes, but nothing happens — you can't "find nothing and replace with something." Every position in a string technically has a zero-length substring, so Excel just returns the original text unchanged.

If your goal is to add text at a specific position, use REPLACE with num_chars=0: =REPLACE(A2, 5, 0, "-inserted-") inserts "-inserted-" before character 5 without removing anything.

Can I use SUBSTITUTE on numbers?

Yes — Excel converts the number to text before processing. =SUBSTITUTE(1234567, "1", "9") returns "9234567" as text.

To convert back to a number, wrap with VALUE: =VALUE(SUBSTITUTE(A2, ",", "")) is the standard way to strip commas from imported number strings so they sum correctly.

Is there a limit to how deeply I can nest SUBSTITUTE?

Excel's overall formula limit is 8,192 characters and 64 nesting levels. In practice, nesting more than ~7 SUBSTITUTEs becomes unreadable well before you hit any technical limit.

For anything beyond 4–5 nested calls, switch to LET (Excel 365/2021+) for named intermediates, or a helper column per step. Your future-self will thank you.

How do I count occurrences of a character using SUBSTITUTE?

The pattern: =LEN(A2) - LEN(SUBSTITUTE(A2, "x", "")). Length before minus length after removing the character equals the count. Change "x" to whatever you want to count.

Great for counting delimiters (commas in CSV row), word count minus one (spaces), or validating email format (should have exactly one "@").

Does SUBSTITUTE work with CHAR(160) non-breaking spaces?

Yes — and it's the only reliable Excel function that handles CHAR(160). TRIM ignores it, CLEAN ignores it, but SUBSTITUTE can convert it explicitly: =SUBSTITUTE(A2, CHAR(160), " ").

The full defensive cleanup: =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))). SUBSTITUTE converts CHAR(160) to regular spaces, CLEAN strips other non-printing characters, TRIM handles the leftover spaces. This is the industry-standard formula for imported-data cleanup.

Does SUBSTITUTE work on entire ranges?

In Excel 365 and 2021+, yes — it spills naturally: =SUBSTITUTE(A2:A100, "-", "") in a single cell processes the entire range. In older Excel versions, you need to enter the formula in each cell (or use it as an array formula with Ctrl+Shift+Enter).

For very large ranges (10,000+ rows), the array-spill approach can slow down recalc — a helper column with a per-cell formula usually performs better for big datasets.

Should I use SUBSTITUTE or Power Query for bulk cleanup?

SUBSTITUTE is great for one-off cleanup and cases where the raw column needs to stay untouched. Power Query is better for repeatable, multi-step cleanup pipelines you'll run on new data monthly or weekly.

Rule of thumb: 1–2 SUBSTITUTE steps on a small dataset → formula. 5+ transformation steps or hundreds of thousands of rows → Power Query. Both approaches are valid; both are worth learning.

Never Write Nested SUBSTITUTE by Hand Again

Excel Wizard writes SUBSTITUTE patterns — nested cleanup, Nth-occurrence targeting, CHAR(160) fixes, LET-formatted for readability — from a plain-English prompt. AI-powered formulas, right in your ribbon.

Get Excel Wizard Add-in →