What to put in its place. Two empty quotes remove it.
instance_num
Optional. Which occurrence to change. Left out, every one is changed.
When to use it
Cleaning codes and references: taking out dashes, slashes or spaces that came in with the data.
What trips people up
It is case sensitive, so "abc" will not match "ABC". It also replaces every match unless you name which one you want. And the result is text, so a number cleaned this way will no longer add up.
What is the difference between SUBSTITUTE and REPLACE?
SUBSTITUTE finds text by what it says. REPLACE changes characters by position, such as the fourth to sixth character. Use SUBSTITUTE when you know the text and REPLACE when you know where it is.
How do I remove two different characters at once?
Put one SUBSTITUTE inside another: =SUBSTITUTE(SUBSTITUTE(A2,"-",""),"/","") removes the dashes, then the slashes.
How do I turn the cleaned result back into a number?
Wrap it in VALUE: =VALUE(SUBSTITUTE(A2," ","")). Without that the result stays text and will not add up.
Related functions
TRIM — TRIM removes spaces from the start and end of a piece of text, and squeezes any run of spaces in the middle down to one.
TEXTBEFORE — TEXTBEFORE returns everything that comes before a marker you name, such as a space, a dash or an at sign.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
