Need to clean several patterns in one go — strip $, commas, and stray symbols? Nest SUBSTITUTE calls so each one feeds the next, doing a whole list of replacements in a single formula.
The example
A messy currency string reduced to a clean number-ready value.
| A | B | |
|---|---|---|
| 1 | Raw | Cleaned |
| 2 | $ 1,250.00 | 1250.00 |
| 3 | $ 9,800.50 | 9800.50 |
The formula
Stack the replacements, inside out:
How it works
Nested SUBSTITUTE runs from the inside out:
- The innermost SUBSTITUTE runs first — here, removing the
$. - Its result becomes the input to the next SUBSTITUTE, which removes the comma, and so on outward.
- Each call handles one find/replace pair, so chain as many as you need (Excel allows deep nesting).
- Order can matter: replace longer or more specific patterns before shorter ones to avoid partial-match surprises.
Too many to nest? Build a small lookup table of find/replace pairs and reduce over it — in Excel 365, REDUCE with SUBSTITUTE applies the whole list cleanly. For one-off jobs, Find & Replace (Ctrl+H) run several times is simplest.
Try it: interactive demo
Type a value; $, commas, and spaces are stripped.
Variations
Replace, don’t delete
Swap underscores for spaces:
Nth occurrence only
SUBSTITUTE’s 4th argument:
Then make it a number
Convert the cleaned text:
Pitfalls & errors
SUBSTITUTE is case-sensitive. “Inc” and “inc” are different. Match the exact casing, or normalize with UPPER/LOWER first.
Order of replacements. Replacing a substring that’s part of another pattern can cascade unexpectedly — do the most specific replacements first.
SUBSTITUTE vs REPLACE. SUBSTITUTE swaps matching text; REPLACE swaps by position. Use SUBSTITUTE for find-and-replace by content.
Practice workbook
Frequently asked questions
How do I replace multiple characters at once in Excel?
Is SUBSTITUTE case-sensitive?
What's the difference between SUBSTITUTE and REPLACE?
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