SUBSTITUTE function
SUBSTITUTE replaces specific text with other text wherever it appears, or only the nth time, and returns the updated text. It is a workhorse for cleaning imports, such as removing dashes.
Example
Read it as a sentence: in a-b-c, replace the second hyphen with a slash, which gives a-b/c.
A
B
C
D
1
a-b/c
2
3
Syntax
SUBSTITUTE(text, old_text, new_text, [instance_num])
Arguments in [square brackets] are optional.
-
text(required) - The text to change.
-
old_text(required) - The text to replace.
-
new_text(required) - What to put instead.
-
instance_num(optional) - Which occurrence to replace; omit to replace all.
Returns: The updated text.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=SUBSTITUTE("a-b-c","-","+") | a+b+c | All |
=SUBSTITUTE("a-b-c","-","+",2) | a-b+c | Instance 2 |
=SUBSTITUTE("aAa","a","x") | xAx | Case-sensitive |
=SUBSTITUTE("a-b","-","+",5) | a-b | Instance past count |
=SUBSTITUTE(1231,1,9) | 9239 | Numbers |
=SUBSTITUTE("abc","","x") | abc | Empty old_text |
=SUBSTITUTE("aaa","aa","b") | ba | Overlapping |
=SUBSTITUTE("a-b","-","+",0) | #VALUE! | Instance 0 |
Watch out for
- Case-sensitive.
- Replaces every occurrence unless instance_num is given.
Where it works
- Excel
- SUBSTITUTE is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Text functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .