FormuladrillSUBSTITUTE
SUBSTITUTE(text, old_text, new_text, [instance_num]) replaces every occurrence of old_text in a string with new_text, or only the nth occurrence when instance_num is given. Use an empty string as new_text to delete characters. It is case sensitive and does not accept wildcards.
Syntax
SUBSTITUTE(text, old_text, new_text, [instance_num])| Part | What it is |
|---|---|
text | The cell or string to change. |
old_text | The text to find. Case sensitive. |
new_text | The replacement. Use "" to remove old_text. |
instance_num | Which occurrence to replace. Omit to replace all. |
Examples
=SUBSTITUTE("2026-01-15", "-", "")Result: 20260115
Removes every dash. Wrap in VALUE to get a number.
=SUBSTITUTE("a,b,c", ",", ";", 2)Result: a,b;c
Only the second comma is replaced.
=LEN(A2) - LEN(SUBSTITUTE(A2, ",", ""))Result: The number of commas in A2, which is one less than the number of items in a comma list
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", ""), "-", ""))Result: 5551234567 when A2 is (555) 123-4567 with no space
Nest one SUBSTITUTE per character to strip.
Common mistakes
- Nothing is replaced: SUBSTITUTE is case sensitive, so "North" does not match "north". Wrap text in LOWER first or use Find and Replace.
- Using SUBSTITUTE to change a position: it works on text, not positions. REPLACE(text, start, count, new) changes characters at a position.
- The result stays text after removing characters from a number; wrap in VALUE.
- Replacing a space with nothing also removes the spaces between words; to collapse extra spaces only, use TRIM.
Practice SUBSTITUTE
These drills check your answer on live data, free, no account needed.
- Digits onlyRemove the dashes from each phone number. Fill B2 and B3.
Questions
What is the difference between SUBSTITUTE and REPLACE?
SUBSTITUTE finds text and swaps it. REPLACE swaps a fixed number of characters at a fixed position, for example REPLACE(A2, 1, 3, "XYZ") replaces the first three characters.
How do I remove a specific character from a cell?
=SUBSTITUTE(A2, "-", "") removes every dash. Nest several calls to remove more than one character.
How do I replace a line break in a cell?
=SUBSTITUTE(A2, CHAR(10), " ") replaces each line break with a space.
Related
- TRIMHow the Excel TRIM function removes leading, trailing and repeated spaces, why lookups fail without it, and how to remove the non-breaking spaces it leaves.
- LEN, LEFT, RIGHT, MIDHow to pull characters from the start, end or middle of a cell with LEFT, RIGHT and MID, measure length with LEN, and use FIND to split text at a character.
- TEXTHow the Excel TEXT function turns a number or date into text in a format you choose, with examples for dates, thousand separators, percentages and padding.
- CONCAT and &How to join text in Excel with CONCAT, CONCATENATE and the & operator, with examples for full names, email addresses, numbered IDs and a formatted date.