SUBSTITUTE Function in Excel: Replace Specific Text

📎 This article includes 1 downloadable practice file ↓

TextLevel: BeginnerAvailable in: Excel 2007+

In this article

1. Syntax
2. Examples
3. Example 1
4. Example 2
5. Power combo
6. SUBSTITUTE in practice
7. Where you will use it
8. Worked example
9. Mistakes people make
10. Related functions
11. Continue learning

SUBSTITUTE replaces existing text with new text — all occurrences or just the n-th one.

Syntax

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Argument	What it means
instance_num	Which occurrence to replace (all if omitted).

Examples

Example 1

=SUBSTITUTE(A2, "-", "")

Remove all dashes from a phone number.

Example 2

=LEN(A2)-LEN(SUBSTITUTE(A2, ",", ""))+1

Count items in a comma list.

Power combo

=TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", 100)), 100))

Last word of a text in every Excel version.

SUBSTITUTE in practice

Where you will use it

• Remove spaces or dashes from mobile numbers
• Replace old product names
• Count occurrences of a character with LEN

Worked example

Text	Formula	Result
98765 43210	=SUBSTITUTE(SUBSTITUTE(A2," ",""),"-","")	9876543210
91234-56789	=SUBSTITUTE(SUBSTITUTE(A3," ",""),"-","")	9123456789
Pvt. Ltd.	=SUBSTITUTE(SUBSTITUTE(A4," ",""),"-","")	Pvt.Ltd.

Results calculated in Excel.

Mistakes people make

Mistake	What to do instead
Case sensitivity	SUBSTITUTE is case-sensitive; LOWER both sides if needed
Replacing only one occurrence	Use the 4th argument for the instance

Related functions

REPLACE · TRIM · LEN

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

Continue learning

All Text guides →

REGEXREPLACE Function in Excel: Find and Replace With PatternsREGEXREPLACE replaces text matching a pattern — strip every non-digit, collapse repeated spaces, mask…T Function in Excel: Keep Text, Blank Out NumbersT returns text unchanged and anything else as an empty string. Rarely needed alone,…NUMBERVALUE Function in Excel: Convert Text Numbers With Any SeparatorsNUMBERVALUE converts text to a number using separators you specify — essential for European-style…VALUE Function in Excel: Convert Text to a NumberVALUE turns text that looks like a number into a real number, so SUM…

📎 Practice files for this article

• 📗
  Practice workbook: Text & cleaning24 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.
  ⬇ XLSX · 35 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.