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.

Excel CSE · 2 min read
In this guide
  1. SUBSTITUTE syntax
  2. Examples
  3. Several replacements
  4. Count occurrences
  5. Text to numbers
  6. SUBSTITUTE vs. REPLACE
  7. FAQ

SUBSTITUTE syntax

=SUBSTITUTE(text, old_text, new_text, [instance_num])
ArgumentWhat it means
textThe text or cell to change.
old_textThe text to find. SUBSTITUTE is case-sensitive.
new_textThe replacement. Use "" to remove the old text.
instance_num
(optional)		Which occurrence to replace. Leave it out to replace all of them.

Examples

GoalFormulaResult
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.