Function guides · Text
SUBSTITUTE function
Replaces specific text inside a string with new text.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])
| Argument | What it is |
|---|---|
text | The text or cell to change. |
old_text | The text to replace. Case sensitive. |
new_text | The replacement. Use "" to delete. |
instance_num | Optional. Which occurrence to replace. Omitted, every occurrence is replaced. |
What SUBSTITUTE does
SUBSTITUTE swaps one piece of text for another wherever it appears inside a cell. You say what to look for and what to put there instead.
It matches by content, so it does not need to know where the text is. That makes it the tool for removing dashes from phone numbers, replacing abbreviations, deleting stray characters, and fixing a symbol that appears throughout a column.
Without the optional instance number it replaces every match. With it, only the one you name. The matching is case sensitive. Capital letters and lowercase letters are different characters.
Excel also has Find and Replace in the Home ribbon, which does a similar job once, in place. Use that for a one-off cleanup of a finished file. Use SUBSTITUTE when the cleanup must repeat as the source data changes, such as a monthly import with the same stray characters each time.
To replace several things, nest the calls, working from the inside out. Keep the nesting shallow. A cleaning step that needs more than four replacements usually belongs in a small lookup table with a repeating formula, or in Power Query.
SUBSTITUTE examples
1. Strip dashes from a phone number
| A | B | |
|---|---|---|
| 1 | Phone | Digits only |
| 2 | 808-555-0114 | 8085550114 |
Replacing the dash with an empty string deletes it. Every occurrence changes, which is what you want for a phone number.
2. Replace only the second occurrence
| A | B | |
|---|---|---|
| 1 | ID | |
| 2 | HRD-0932-B | HRD-0932/B |
The fourth argument picks which match to change. This turns the second dash in "HRD-0932-B" into a slash and leaves the first alone.
3. Clean an invisible character
| A | B | |
|---|---|---|
| 1 | Raw | |
| 2 | Kona Print | Kona Print |
Text pasted from a web page may contain non-breaking spaces, character 160, that TRIM cannot remove. Substituting them with ordinary spaces lets TRIM finish the job.
Common SUBSTITUTE mistakes
- Case sensitivity. SUBSTITUTE(A2,"west","East") does not change "West". Wrap A2 in LOWER or UPPER if case varies.
- Confusing it with REPLACE. SUBSTITUTE replaces by content. REPLACE replaces by position and length.
- Chaining too many. Nesting more than three or four SUBSTITUTEs gets unreadable. Consider a lookup table or Find and Replace for a one-off.
- Results are text. Removing dashes from a phone number leaves text. Use VALUE for a number.
- Forgetting that it changes the formula, not the cell. SUBSTITUTE returns a new value. The original is unchanged until you paste values.
SUBSTITUTE on Excel job tests
"Remove the dashes", "replace St. with Street" and "strip the dollar signs and convert to numbers" are the usual prompts. Several replacements are nested SUBSTITUTE calls, with VALUE on the outside when the end product is a number.
If a lookup still fails after TRIM, try SUBSTITUTE with CHAR(160), the non-breaking space that TRIM ignores. To count how often a character appears, compare LEN of the text with LEN after removing the character.
Remember that SUBSTITUTE is case sensitive.
Related functions
SUBSTITUTE questions
- What is the difference between SUBSTITUTE and REPLACE?
- SUBSTITUTE swaps specific text wherever it appears. REPLACE swaps characters at a given position and length.
- How do I delete characters with SUBSTITUTE?
- Use an empty string as the new text:
SUBSTITUTE(A2,"-",""). - Is SUBSTITUTE case sensitive?
- Yes. Convert the text with LOWER or UPPER first if the case varies.
- How do I replace several different characters?
- Nest the function:
SUBSTITUTE(SUBSTITUTE(A2,"-","")," ","").
Practice SUBSTITUTE
Try it in a working grid: the free lesson LEFT, RIGHT and MID: pull out part of a cell (module 9 of Everyday Excel) has checked SUBSTITUTE exercises.
Practice this function