What it does
SUBSTITUTE replaces the text it looks for with the new text everywhere it appears. It looks at content, not position: "USD" is found wherever it is. If the new text is "", the piece is simply removed; that is how it is used most often in data cleaning.
The fourth argument changes just one occurrence: =SUBSTITUTE(A2,"-","/",2) turns only the second hyphen into a slash. SUBSTITUTE is case-sensitive; if you type "usd", "USD" is left alone.
If you know where the part is rather than what it says (say, characters 5 to 8 of a code), use REPLACE. To replace several different pieces, nest SUBSTITUTE functions; each one works on the result of the one inside it.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])=YERİNEKOY(metin; eski; yeni; [kaçıncı])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
text | Required | The text, or cell, to change. |
old_text | Required | The text to replace. It is case-sensitive and wildcards are not used. |
new_text | Required | The text to put in its place. Use "" to delete the old text. |
instance_num | Optional | Which occurrence to replace. If omitted, every occurrence is replaced; it cannot be less than 1. |
Examples
1Remove "USD" from an amount and turn it into a number
Amounts exported from another system arrive as text with "USD" attached. SUBSTITUTE deletes " USD", and the two minus signs in front turn what is left into a number.
=--SUBSTITUTE(A2," USD","")| A | B | |
|---|---|---|
| 1 | Amount (text) | Amount |
| 2 | 1250 USD | 1,250.00 |
| 3 | 980 USD | 980.00 |
| 4 | 12400 USD | 12,400.00 |
| 5 | 75 USD | 75.00 |
- B2 (filled down to B5)
=--SUBSTITUTE(A2," USD","")
The values in column B are now numbers: 1,250.00, 980.00, 12,400.00, 75.00. You can add them up with SUM.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Make different delimiters consistent
Some codes are separated with spaces, others with slashes. The inner SUBSTITUTE turns spaces into hyphens and the outer one does the same for slashes.
=SUBSTITUTE(SUBSTITUTE(A2," ","-"),"/","-")| A | B | |
|---|---|---|
| 1 | Code as Typed | Clean Code |
| 2 | KBL 1525 SY | KBL-1525-SY |
| 3 | PRZ/0210/BY | PRZ-0210-BY |
| 4 | SGT-0016 BY | SGT-0016-BY |
| 5 | KNL 2040/GR | KNL-2040-GR |
- B2 (filled down to B5)
=SUBSTITUTE(SUBSTITUTE(A2," ","-"),"/","-")
Every code ends up in the same format: KBL-1525-SY, PRZ-0210-BY, SGT-0016-BY, KNL-2040-GR. They can now be matched against the price list.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Remove only the first hyphen
The new stock system drops the first hyphen of each code. The fourth argument is 1, so only the first hyphen is removed and the second stays.
=SUBSTITUTE(A2,"-","",1)| A | B | |
|---|---|---|
| 1 | Old Code | New Code |
| 2 | KBL-1525-SY | KBL1525-SY |
| 3 | PRZ-0210-BY | PRZ0210-BY |
| 4 | SGT-0016-BY | SGT0016-BY |
| 5 | KNL-2040-GR | KNL2040-GR |
- B2 (filled down to B5)
=SUBSTITUTE(A2,"-","",1)
B2 shows KBL1525-SY. Without the fourth argument, both hyphens would be removed.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- Nothing changes
Cause: old_text does not appear exactly as written: the case is different or another character sits in between. Text copied from the web may contain non-breaking spaces (
CHAR(160)) instead of ordinary ones.Fix: Copy the text straight from the source; for non-breaking spaces use
=SUBSTITUTE(A2,CHAR(160)," ").- #VALUE!
Cause: instance_num is less than 1 or is not a number.
Fix: Use a whole number of 1 or more, or leave the argument out.
- Amounts will not add up
Cause: SUBSTITUTE always returns text; removing " USD" from "1250 USD" does not turn the value into a number by itself.
Fix: Convert the result with
--SUBSTITUTE(A2," USD","")or VALUE.
Good to know
In Nexell
Get Nexell to do it from a sentence
Add a column that removes "USD" from the Amount column and turns the values into numbers
Convert to tableBring your table into the tool and type this. Nexell strips the "USD" text, turns the amounts into numbers and shows the converted values in the preview; in the downloaded file you can total and summarise that column.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I replace several different characters with SUBSTITUTE?
Nest the functions; the inner one makes the first change and the outer one works on its result: =SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","") removes both brackets.
What is the difference between SUBSTITUTE and REPLACE?
SUBSTITUTE finds the text to change by its content (wherever "USD" appears). REPLACE works by position: you say which character to start at and how many characters to change.