The SUBSTITUTE function in Excel
SUBSTITUTE swaps one piece of text for another: dashes for slashes, "St." for "Street", or a character for nothing at all to remove it. It's the formula version of Find and Replace, so the original data stays untouched.
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 replace. Case-sensitive. |
| new_text | The replacement. Use "" to delete old_text. |
| instance_numoptional | Replace only this occurrence (1 = first). Leave out to replace all. |
Examples
1. Remove a character
D2fx=SUBSTITUTE(A2,"-","")
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | No dashes | ||
| 2 | SH-4417-BLU | omar.haddad@shopmail.com | SH4417BLU |
Every dash is replaced with nothing.
2. Replace only the second occurrence
D2fx=SUBSTITUTE(A2,"-","/",2)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | Changed | ||
| 2 | SH-4417-BLU | omar.haddad@shopmail.com | SH-4417/BLU |
instance_num 2 leaves the first dash alone.
3. Several replacements: nest them
B2fx=SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","")
| A | B | |
|---|---|---|
| 1 | Phone | Clean |
| 2 | (555) 0142 | 555 0142 |
The inner SUBSTITUTE removes "(", the outer one removes ")".
Common mistakes
Nothing changes. SUBSTITUTE is case-sensitive: "blu" won't replace "BLU".
Fix: Match the case exactly, or wrap the text in UPPER or LOWER first.
Using it to replace by position. SUBSTITUTE works on content, not positions.
Fix: To replace characters at a position, use REPLACE.
Frequently asked questions
What's the difference between SUBSTITUTE and REPLACE?
SUBSTITUTE replaces a given text wherever it appears. REPLACE replaces a number of characters starting at a position.