Excel SUBSTITUTE & REPLACE — Swap by Content vs Swap by Position

TL;DR — Both functions swap text, but they answer different questions. SUBSTITUTE(text, old, new, [instance]) works by content — "replace every "," with nothing," or with the 4th argument, "replace only the 2nd one." REPLACE(text, start, length, new) works by position — "overwrite 5 characters starting at position 9," no matter what's there. Pick the wrong one and you silently change the wrong thing. SUBSTITUTE is case-sensitive; REPLACE doesn't look at content at all. Both return text and work in every Excel version.

=SUBSTITUTE("1,234,567", ",", "")      ' -> "1234567"  (every comma, by content)
=SUBSTITUTE("a-b-c", "-", ":", 2)      ' -> "a-b:c"    (only the 2nd hyphen)
=REPLACE("2026-XX-01", 6, 2, "04")     ' -> "2026-04-01" (by position)

These are the two "find and replace" formulas, and the entire skill is knowing which mental model you're in. Get that right and they're trivial; get it wrong and you'll spend an afternoon wondering why the wrong characters changed.

What you'll learn

The core split: SUBSTITUTE matches content, REPLACE matches position
How SUBSTITUTE's 4th argument targets just the Nth occurrence
Why SUBSTITUTE is case-sensitive, and how that bites
The famous LEN-minus-LEN trick for counting occurrences
When to pair REPLACE with FIND — and when to stop and use TEXTSPLIT

The mental model: a word swap vs a stamp

SUBSTITUTE is find-and-replace by meaning, like pressing Ctrl+H: "find this text, put that text in its place, everywhere it appears." It doesn't care where the matches are, only what they are.

REPLACE is a stamp at a coordinate: "starting at character 9, blot out the next 5 and write this instead." It doesn't care what was there — it only knows the position. If the thing you want to change sits at a predictable spot, use REPLACE. If it's a predictable string that could be anywhere, use SUBSTITUTE.

' Fixed format -> position is reliable -> REPLACE
=REPLACE("ID-000123", 1, 3, "REF-")    ' "REF-000123"

' Known text, unknown location -> content -> SUBSTITUTE
=SUBSTITUTE("Qty: 5 units", "units", "pcs")   ' "Qty: 5 pcs"

That single distinction resolves nearly every "which one do I use?" question.

The rule that unlocks everything: SUBSTITUTE's 4th argument

By default SUBSTITUTE replaces every occurrence — which is what you want for stripping all commas or all spaces out of a value. But its optional 4th argument, instance_num, lets you target exactly one:

=SUBSTITUTE("a-b-c-d", "-", " ")       ' "a b c d"   — all of them
=SUBSTITUTE("a-b-c-d", "-", " ", 3)    ' "a-b-c d"   — only the 3rd hyphen

This is the feature that makes SUBSTITUTE punch above its weight: "change the last separator to a space," "fix only the second slash." It's surgical in a way REPLACE can't be, because REPLACE would need you to compute the position first.

SUBSTITUTE is case-sensitive (REPLACE doesn't care)

A quiet gotcha: SUBSTITUTE matches case exactly. SUBSTITUTE("Apple apple", "apple", "orange") changes only the lowercase one, returning "Apple orange". This is consistent with FIND (also case-sensitive) but the opposite of how the worksheet's = comparison and SEARCH behave, so it catches people. If you need case-insensitive replacement, there's no flag — you normalize first (e.g. SUBSTITUTE(UPPER(A2), "APPLE", ...)) or accept that you must match the exact casing.

REPLACE sidesteps this entirely: it never looks at content, so case is irrelevant.

The trick worth knowing: count occurrences with LEN

Here's the most-loved SUBSTITUTE idiom, and it has nothing to do with replacing. To count how many times a character appears, measure how much shorter the string gets when you remove it:

' How many commas are in A2?
=LEN(A2) - LEN(SUBSTITUTE(A2, ",", ""))

' How many words? (spaces + 1, for a single-spaced string)
=LEN(TRIM(A2)) - LEN(SUBSTITUTE(TRIM(A2), " ", "")) + 1

Remove every comma, see how many characters vanished — that's your count. It's the kind of lateral trick that, once seen, you reuse forever.

When REPLACE needs FIND, and when to stop

REPLACE only knows positions, so the moment the position depends on content, you compute it with FIND:

' Mask everything before the @ in an email, length unknown
=REPLACE(A2, 1, FIND("@", A2) - 1, "*****")

That's the legitimate marriage of position and content. But if you're reaching for SUBSTITUTE to restructure a string — splitting "First,Last,Dept" into columns, say — you've outgrown it. Chaining SUBSTITUTE to fake a split is a classic anti-pattern; TEXTSPLIT (Excel 365) does it in one formula and stays live. And note this is the worksheet REPLACE/SUBSTITUTE, not VBA's Replace, which is case-insensitive by default and has a position argument that truncates — different rules, same name.

The judgment call

Ask one question: am I changing a specific text, or a specific position? Specific text, anywhere → SUBSTITUTE (add the 4th argument to limit it to one occurrence). Specific position → REPLACE (compute the position with FIND if it's not literally fixed). Reaching for the LEN-minus-LEN count? That's SUBSTITUTE earning its keep. Stacking three SUBSTITUTEs to reshape a record? That's your cue to split the string properly instead.

How ExcelMaster helps

"Strip the currency symbols and thousands separators, but keep the decimal," or "mask the account number except the last four" — these are SUBSTITUTE/REPLACE jobs where the trap is matching content when you meant position (or vice versa). ExcelMaster reads the intent in plain English and writes the right one — the instance_num when you mean "just the second," the FIND-driven position when you mean "after the @" — so you don't change the wrong characters. You describe the swap; it picks the model.

Frequently asked questions

What's the difference between SUBSTITUTE and REPLACE?

SUBSTITUTE replaces text by content — find a string, swap it wherever it appears (or just the Nth time). REPLACE replaces by position — overwrite a fixed number of characters starting at a given spot, regardless of content. Use SUBSTITUTE for known text, REPLACE for known positions.

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

Use SUBSTITUTE's 4th argument: =SUBSTITUTE(A2, "-", " ", 2) changes only the second hyphen. REPLACE can't target by occurrence — it only works by position.

Is SUBSTITUTE case-sensitive?

Yes. SUBSTITUTE(A2, "apple", "orange") won't touch "Apple". To replace regardless of case, normalize the text first with UPPER or LOWER, since there's no case-insensitivity flag.

How do I count how many times a character appears in a cell?

Subtract the de-substituted length from the original: =LEN(A2) - LEN(SUBSTITUTE(A2, ",", "")) counts the commas. Removing the character and measuring the difference gives the count.

How do I replace text at a position I don't know in advance?

Compute the position with FIND and feed it to REPLACE: =REPLACE(A2, 1, FIND("@", A2) - 1, "*****") masks everything before the @. This combines REPLACE's position model with FIND's content search.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-06-29.