Home › Excel functions › SUBSTITUTE
SUBSTITUTE in Excel: Replace Text with a Formula
SUBSTITUTE replaces specific text in a cell with other text. Remove dashes from phone numbers, swap decimal points for commas, fix a recurring typo: all without touching the original data.
SUBSTITUTE · In this guide
SUBSTITUTE syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])| Argument | What it means |
|---|---|
| text | The text or cell to change. |
| old_text | The text to find. SUBSTITUTE is case-sensitive. |
| new_text | The replacement. Use "" to remove the old text. |
| instance_num (optional) | Which occurrence to replace. Leave it out to replace all of them. |
Examples
| Goal | Formula | Result |
|---|---|---|
| Remove dashes | =SUBSTITUTE("06-1234-5678", "-", "") | 0612345678 |
| Replace a word | =SUBSTITUTE("Q1 report", "Q1", "Q2") | Q2 report |
| Only the 2nd occurrence | =SUBSTITUTE("a-b-c", "-", "/", 2) | a-b/c |
| Remove all spaces | =SUBSTITUTE(A2, " ", "") | No spaces at all |
Several replacements at once
Nest SUBSTITUTE inside itself, one level per replacement. To remove brackets and dashes from a phone number:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", ""), "-", "")"(020) 123-4567" becomes "020 1234567".
Count how often a character appears
Compare the length before and after removing the character:
=LEN(A2) - LEN(SUBSTITUTE(A2, ",", ""))For "apple,banana,cherry" this returns 2 commas, so there are 3 items. More on LEN in the LEN guide.
Fix numbers with the wrong separator
Imported numbers like "1.234,50" (European style) are text in a US-English Excel. Swap the separators and convert:
=VALUE(SUBSTITUTE(SUBSTITUTE(A2, ".", ""), ",", "."))returns the number 1234.5.
SUBSTITUTE vs. REPLACE vs. Find & Replace
- SUBSTITUTE replaces text by what it is: every "-" in the cell.
- REPLACE replaces text by position:
=REPLACE(A2, 1, 3, "")removes the first three characters. - Ctrl+H (Find & Replace) changes the cells themselves, without a formula. Quicker for one-off clean-ups.
Frequently asked questions
Is SUBSTITUTE case-sensitive?
Yes. SUBSTITUTE(A2, "abc", "x") won't replace "ABC". Wrap the text in LOWER first if case shouldn't matter.
Can SUBSTITUTE use wildcards?
No. It only replaces exact text. Use Find & Replace (Ctrl+H) for wildcard replacements.
How do I replace line breaks?
Line breaks are CHAR(10): =SUBSTITUTE(A2, CHAR(10), ", ") turns them into commas.