How to Use SUBSTITUTE in Excel
Replaces specific text inside a string — clean currency symbols, remove units or swap separators in bulk.
What is SUBSTITUTE in Microsoft Excel?
SUBSTITUTE finds an exact piece of text and swaps it for something else, optionally only on the nth occurrence. Unlike REPLACE, which works by position, SUBSTITUTE works by content, so it survives changes in string length. It is the workhorse for data cleaning and text normalisation.
Formula Syntax
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| text | Required | The original string or cell. |
| old_text | Required | The exact characters to find. Case-sensitive. |
| new_text | Required | What to put in its place. Use "" to delete the found text. |
| instance_num | Optional | Which occurrence to replace. Omit to replace every occurrence. |
When to Use SUBSTITUTE (Real-World Business Scenarios)
- 1
Stripping currency symbols or units before converting text to numbers.
- 2
Swapping one delimiter for another, such as commas to pipes for a CSV export.
- 3
Replacing only the second dash in a code so the first one stays intact.
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Raw Amount | Cleaned |
|---|---|
| ₹1,20,000 | 120000 |
| ₹85,000 | 85000 |
| ₹12,500 | 12500 |
How it works: The inner SUBSTITUTE removes the rupee symbol, the outer one removes commas, and VALUE turns the remaining text into a real number that can be summed.
Common Errors & How to Fix Them
Why it happens: The text is case-sensitive, so "Total" will not match "total", or the character is a look-alike such as a non-breaking space.
Fix: Confirm the exact characters with =CODE(MID(A2,n,1)) and match case precisely.
Why it happens: instance_num was omitted, so every occurrence changed instead of only the target one.
Fix: Pass the occurrence number: =SUBSTITUTE(A2,"-","/",2).
Try SUBSTITUTE Live in Your Browser
No need to launch desktop Excel. Practice formulas with real-time feedback in our HyperFormula sandbox.
Frequently Asked Questions (SUBSTITUTE)
What does the SUBSTITUTE formula do in Excel?
SUBSTITUTE finds an exact piece of text and swaps it for something else, optionally only on the nth occurrence. Unlike REPLACE, which works by position, SUBSTITUTE works by content, so it survives changes in string length. It is the workhorse for data cleaning and text normalisation.
What is the syntax for SUBSTITUTE?
What are common mistakes when using SUBSTITUTE?
Nothing is replaced: The text is case-sensitive, so "Total" will not match "total", or the character is a look-alike such as a non-breaking space. (Confirm the exact characters with =CODE(MID(A2,n,1)) and match case precisely.) Replaced too much: instance_num was omitted, so every occurrence changed instead of only the target one. (Pass the occurrence number: =SUBSTITUTE(A2,"-","/",2).)
Related Excel Formulas
Keep building your formula toolkit with these text functions.
- TEXTJOINJoins a whole range into one string with a delimiter you choose — the clean way to build comma-separated lists.
- TRIMRemoves leading, trailing and repeated spaces — the first fix for lookups that mysteriously return #N/A.
- LEFTExtracts the first characters of a string — pull a prefix, area code or year out of a longer code.
- LENCounts the characters in a cell, including spaces — the quickest way to catch invisible trailing whitespace.
- PROPERCapitalises the first letter of every word — fixes SHOUTED or all-lowercase names from raw imports.
- TEXTSPLITSplits one cell into many by a delimiter and spills the pieces into neighbouring cells — no Text to Columns needed.
Master SUBSTITUTE in Intermediate Excel
Learn SUBSTITUTE along with our formula library, practice files, and verifiable certificates in our complete curriculum.