The Excel SUBSTITUTE function swaps text by value: tell it what to find and what to put in its place, and it replaces every occurrence — or just the one you point at with the optional instance number. It is the go-to tool for reformatting codes, stripping unwanted characters, and the classic count-how-many-times trick. Its position-based twin is REPLACE.
=SUBSTITUTE(A2, "-", ".", 2) changes just the second hyphen. The match is case-sensitive.
Syntax
| Argument | Description | |
|---|---|---|
text | Required | The text (or cell reference) to work on. The original cell is never changed. |
old_text | Required | The text to find. Matching is case-sensitive: "TX" will not match "tx". |
new_text | Required | What to put in its place. Use "" to delete every occurrence. |
instance_num | Optional | Which occurrence to replace (1 = first, 2 = second…). Omit it and every occurrence is replaced. |
SUBSTITUTE vs REPLACE: SUBSTITUTE finds text by value (“swap every hyphen”); REPLACE overwrites text by position (“overwrite characters 4–6”). If you know what to change, use SUBSTITUTE; if you know where, use REPLACE.
Replace every occurrence
Phone numbers in A2:A5 use dashes, but the new directory wants dots. One formula in B2, filled down, converts them all:
| A | B | |
|---|---|---|
| 1 | Phone (dashes) | Phone (dots) |
| 2 | 214-555-0147 | 214.555.0147 |
| 3 | 817-555-0198 | 817.555.0198 |
| 4 | 972-555-0023 | 972.555.0023 |
| 5 | 469-555-0061 | 469.555.0061 |
Pass "" as new_text to delete instead of swap — the fastest way to strip spaces from an ID:
Need only the surplus spaces gone, not all of them? That’s TRIM’s job.
Try it: interactive SUBSTITUTE demo
Type your own old_text and new_text, then pick which occurrence to replace.
Target one occurrence — and the counting trick
The fourth argument turns a blanket swap into a sniper shot. To change only the second "2025" in a label:
The classic counting trick. SUBSTITUTE plus LEN counts how many times something appears: remove every occurrence, see how much shorter the text got, and divide by the length of the search text:
Counting delimiters tells you how many items a list holds — one more than the comma count:
Nest SUBSTITUTE to clean several characters in one pass:
Splitting instead of counting? If you actually need the items, not the count, TEXTSPLIT spills each one into its own cell (Excel 365).
Errors & common pitfalls
Pitfall: it’s case-sensitive. =SUBSTITUTE(A2,"usa","USA") leaves "Usa" and "USA" untouched. There is no switch to relax this — either normalize the case first (UPPER/LOWER) or chain a SUBSTITUTE per variant.
Pitfall: no error when nothing matches. If old_text isn’t found — or instance_num is bigger than the number of occurrences — SUBSTITUTE quietly returns the text unchanged. Great for chaining, sneaky for debugging.
Pitfall: the result is text. =SUBSTITUTE("1,234",",","") returns the text “1234”, which SUM ignores. Convert it back with --SUBSTITUTE(...) or VALUE() before doing math.
#VALUE! — bad instance_num. instance_num must be 1 or greater. Zero or a negative number returns #VALUE!.
Pitfall: formatting is invisible to SUBSTITUTE. A cell displaying $1,234 may actually contain 1234 — the $ and comma are number formatting, not characters, so there is nothing to substitute. SUBSTITUTE only sees the underlying value.
Practice workbook
Frequently asked questions
What's the difference between SUBSTITUTE and REPLACE?
Is SUBSTITUTE case-sensitive?
old_text must match the case exactly: searching for "tx" will not find "TX". To do a case-insensitive swap, normalize first — e.g. =SUBSTITUTE(UPPER(A2),"TX","Texas") — or chain one SUBSTITUTE per case variant.How do I count how many times a character appears in a cell?
=(LEN(A2)-LEN(SUBSTITUTE(A2,"-","")))/LEN("-"). Removing every occurrence shrinks the text by one length per hit, so the difference divided by the search text's length is the count.How do I remove all spaces from a cell?
=SUBSTITUTE(A2," ",""). Note this removes every space, including the ones between words. To keep single spaces and only strip the extras, use TRIM instead.Can SUBSTITUTE replace several different characters at once?
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")",""),"-","") cleans three characters in one formula. Each inner result feeds the next layer.Why doesn't my SUM work after SUBSTITUTE?
=--SUBSTITUTE(A2,",","") turns the text "1234" into the number 1234.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class