What it does
SUBSTITUTE replaces specific text inside a cell.
Syntax or pattern
=SUBSTITUTE(text, old_text, new_text, [instance_num])✅
5 practical examples
1
Remove dashes from codes
Turn product codes into plain text.
=SUBSTITUTE(A2,"-","")Useful before matching IDs.
2
Replace old labels
Change “Pending” to “Open”.
=SUBSTITUTE(A2,"Pending","Open")Works on exact text fragments.
3
Remove spaces
Delete all spaces from a code.
=SUBSTITUTE(A2," ","")Different from TRIM, which keeps single spaces.
4
Replace only first instance
Replace the first slash only.
=SUBSTITUTE(A2,"/","-",1)The final argument controls which instance is replaced.
5
Normalize phone numbers
Remove common punctuation.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-",""),"(",""),")","")Nested SUBSTITUTE can clean simple patterns.
Common mistakes to avoid
- Not handling missing delimiters.
- Forgetting that some text functions return text, not numbers.
- Using fixed positions when the text layout is not consistent.
Related Excel examples
FAQ
Can text formulas work with Excel Tables?
Yes. Table references can make text-cleaning formulas easier to understand.