IntermediateText
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])| Argument | What it means |
|---|---|
text | The text or cell. |
old_text | What to replace. |
new_text | What to put instead. |
instance_num | Optional. Replace only the 1st, 2nd... match. |
Examples with results
Every result below is calculated by the practice engine on the sample data shown, not typed in by hand.
Examples that need no data
=SUBSTITUTE("+971 50 123 4567"," ","")Remove spaces from a phone number
=SUBSTITUTE("a-b-c","-","/",2)Replace only the second dash
Try it yourself
Change the formula or switch to other sample data.
Press Calculate to see the result.
The practice box runs in your browser with BDK’s own calculator, which covers about 60 common functions. Cell references work as in Excel (B2, B2:B9). Your formulas are not saved or sent anywhere. For anything unusual, check the result in Excel itself.
How people use SUBSTITUTE at work
- Administration
Remove dashes from a code.
Data: Stock levels (fictional)=SUBSTITUTE(A2,"-","") - Finance and accounting
Turn a comma decimal into a point.
=VALUE(SUBSTITUTE("12,5",",","."))
Common mistakes and tips
- SUBSTITUTE is case sensitive. REPLACE works by position instead of by matching text.
Works in
All versions of Excel, and Google Sheets.
Need Something Different? Let’s Talk.
Looking for different features or something built around your workflow? Share your idea with us.