The SUBSTITUTE function in Excel
SUBSTITUTE replaces every occurrence of one piece of text with another, or only a specific occurrence if you say which. Replace with "" and it removes the text instead. It's a workhorse for cleaning imported data, like standardising phone numbers or stripping symbols, and with LEN it can count how often a character appears.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])
| text | The text or cell to change. |
|---|---|
| old_text | The text to replace, in quotes. It must match exactly, including capitals. |
| new_text | What to put in its place. Use "" to delete it. |
| instance_num | Optional. Which occurrence to replace, like 2 for the second one. If left out, every occurrence is replaced. |
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
These phone numbers were exported with dots. In C2, use SUBSTITUTE to replace every "." in the number in B2 with a hyphen ("-").
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Phone (clean) | ||||||
| 2 | 415.555.0132 | ||||||
| 3 | 212.555.0187 | ||||||
| 4 | 646.555.0144 | ||||||
| 5 | 312.555.0109 | ||||||
| 6 | |||||||
| 7 | |||||||
| 8 |
Tip: SUBSTITUTE needs the cell, the text to find and the text to put in its place, each in that order.
Examples
=SUBSTITUTE(A3,".","-")→ "555-010-9921"Replaces every dot with a hyphen, so the number matches the format used in the rest of the list.
=SUBSTITUTE(B2,"-","/",2)→ "TSH-BLK/M"The 4th argument limits the change to the second hyphen only. The first one stays as it is.
=LEN(C2)-LEN(SUBSTITUTE(C2,";",""))+1→ 3Removing the semicolons shortens the text by one per semicolon. Add 1 and you have the number of tags.
Example data
| 1 | Phone | SKU | Tags |
|---|---|---|---|
| 2 | (555) 010-4477 | TSH-BLK-M | red; blue; green |
| 3 | 555.010.9921 | TSH-WHT-L | sale |
Common mistakes
Capitals don't match
SUBSTITUTE is case-sensitive, so
SUBSTITUTE(A2,"inc","Inc")won't change "INC". Clean the case first with LOWER or UPPER if it varies.The result is text, not a number
SUBSTITUTE("$1,200","$","")returns the text "1,200", not a number. Wrap it in VALUE before doing math with it.Using SUBSTITUTE when you mean REPLACE
SUBSTITUTE swaps text that matches. REPLACE swaps characters at a position, whatever they are. If you know where the change goes but not what's there, use REPLACE.
FAQ
How do I remove a character from a cell?+
Replace it with empty quotes: =SUBSTITUTE(A2,"-","") deletes every hyphen.
How do I replace several different characters?+
Nest one SUBSTITUTE inside another: =SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","") removes both brackets.
How do I replace only the first or last occurrence?+
Use instance_num: =SUBSTITUTE(A2,"-"," ",1) changes only the first hyphen. For the last one, count them first with the LEN trick and use that count as the instance.
Lesson
Master SUBSTITUTE step by step: Cleaning messy text
Fix stray spaces, random capitals and inconsistent separators in imported data with TRIM, PROPER, UPPER, LOWER and SUBSTITUTE.