Text function
Excel SUBSTITUTE function
SUBSTITUTE swaps one piece of text for another inside a cell. By default it replaces every match, or you can choose which one. Use it to strip dashes from phone numbers, rename codes, or remove unwanted characters.
=SUBSTITUTE(text, old_text, new_text, [instance_num])Arguments
text- The text or cell to change.
old_text- The text to replace. It is case sensitive.
new_text- The replacement. Use "" to delete the old text.
instance_num- Which match to replace, like 2 for the second. Leave it out to replace all.
SUBSTITUTE examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | |
|---|---|---|---|
| 1 | Phone | Item | Path |
| 2 | (555) 201-7788 | Blue Shirt - Blue Trim | reports/2025/q1/sales.csv |
| 3 | 555.310.4421 | Red Mug | reports/2025/q2/sales.csv |
Result: 555-310-4421
Replaces every dot with a dash: "555-310-4421".
Result: Navy Shirt - Navy Trim
Both "Blue"s become "Navy".
Result: Blue Shirt - Navy Trim
With instance_num 2, only the second "Blue" changes.
Result: 5552017788
Nested SUBSTITUTEs strip brackets, spaces and dashes: "5552017788".
How to use SUBSTITUTE
- Type =SUBSTITUTE( and select the cell.
- Add the text to find, in quotes.
- Add the replacement in quotes, or "" to delete.
- Optionally add which instance to replace, then close the bracket.
Common SUBSTITUTE mistakes
Case does not match
SUBSTITUTE is case sensitive, so "blue" will not replace "Blue". Match the case exactly.
Confusing it with REPLACE
SUBSTITUTE swaps by matching text. REPLACE swaps by position and length.
Numbers come back as text
The result is always text. Wrap it in VALUE if you need a number.
SUBSTITUTE FAQ
How do I remove a character from a cell in Excel?
Replace it with empty text: =SUBSTITUTE(A2,"-","") removes every dash.
What is the difference between SUBSTITUTE and REPLACE in Excel?
SUBSTITUTE finds text by content, like every "-". REPLACE changes characters at a fixed position, like characters 4 to 6.
How do I replace multiple different characters at once?
Nest SUBSTITUTE calls, one per character: =SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","").
Practice SUBSTITUTE in these lessons
Guides that use SUBSTITUTE
- How to remove spaces in Excel
- How to capitalize the first letter in Excel
- How to remove characters from a cell in Excel
- How to extract numbers from a string in Excel
Related functions
Updated