To strip out unwanted characters — dashes and parentheses from phone numbers, currency symbols, stray punctuation — nest SUBSTITUTE calls, each replacing one character with nothing. It’s the reliable way to clean codes and IDs.
SUBSTITUTE(…, char, ) deletes one character; nest them to remove several.
The example
Phone numbers stripped to digits only.
| A | B | |
|---|---|---|
| 1 | Raw | Digits only |
| 2 | (555) 123-4567 | 5551234567 |
| 3 | 555-987-6543 | 5559876543 |
The formula
Cleaned to digits:
How it works
Each SUBSTITUTE peels off one character:
SUBSTITUTE(A2, "(", "")removes every open parenthesis by replacing it with an empty string.- Wrap that in another SUBSTITUTE for
")", another for"-", another for" "— each layer strips one more character. - The innermost runs first, so the result of one feeds into the next.
- What’s left is just the characters you didn’t remove — here, the digits.
Keep only digits (Excel 365): instead of removing each junk character, keep the wanted ones: =TEXTJOIN("",1,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,"")) keeps numeric characters and drops everything else.
Try it: interactive demo
Type text and the characters to remove (no separators).
Variations
Remove all non-digits (365)
Keep only the numbers:
Remove line breaks
Strip CHAR(10):
Remove a word, not a character
SUBSTITUTE works on strings too:
Pitfalls & errors
Nesting gets unwieldy. For many characters, the formula grows long. The 365 “keep only digits” approach is cleaner when you want one character class.
SUBSTITUTE is case-sensitive. Removing “inc” won’t catch “Inc.” Match the case, or LOWER the text first.
Result is text. A “cleaned” phone number is still text; wrap in VALUE only if you truly need a number (and don’t mind losing leading zeros).
Practice workbook
Frequently asked questions
How do I remove specific characters from text in Excel?
How do I keep only the numbers in a cell?
How do I remove line breaks from a cell?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class