Skip to content

Excel function · Text

Excel SUBSTITUTE function

SUBSTITUTE replaces one piece of text with another wherever it appears: removing "USD" from amounts, turning spaces in a code into hyphens, making delimiters consistent.

In Turkish Excel: YERİNEKOY

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

English Excel=SUBSTITUTE(text, old_text, new_text, [instance_num])
Turkish Excel=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

ArgumentRequiredDescription
textRequiredThe text, or cell, to change.
old_textRequiredThe text to replace. It is case-sensitive and wildcards are not used.
new_textRequiredThe text to put in its place. Use "" to delete the old text.
instance_numOptionalWhich 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.

B2fx=--SUBSTITUTE(A2," USD","")
Remove "USD" from an amount and turn it into a number: example table
AB
1Amount (text)Amount
21250 USD1,250.00
3980 USD980.00
412400 USD12,400.00
575 USD75.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.

B2fx=SUBSTITUTE(SUBSTITUTE(A2," ","-"),"/","-")
Make different delimiters consistent: example table
AB
1Code as TypedClean Code
2KBL 1525 SYKBL-1525-SY
3PRZ/0210/BYPRZ-0210-BY
4SGT-0016 BYSGT-0016-BY
5KNL 2040/GRKNL-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.

B2fx=SUBSTITUTE(A2,"-","",1)
Remove only the first hyphen: example table
AB
1Old CodeNew Code
2KBL-1525-SYKBL1525-SY
3PRZ-0210-BYPRZ0210-BY
4SGT-0016-BYSGT0016-BY
5KNL-2040-GRKNL2040-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

  • To count how many times a character appears, combine SUBSTITUTE with LEN: =LEN(A2)-LEN(SUBSTITUTE(A2,"-","")).
  • To remove every space, use =SUBSTITUTE(A2," ",""); to remove only the extra ones, use TRIM.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add a column that removes "USD" from the Amount column and turns the values into numbers

Convert to table

Bring 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.

Related functions

All text functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free