SUBSTITUTE function
SUBSTITUTE replaces specific text with new text everywhere it appears, or only at the occurrence you choose. Use it to strip dashes from phone numbers, swap an old product name for a new one, or remove characters that block a lookup.
- Available in
- Every current version
- Category
- Text functions
- Returns
- Text with the replacements made.
- Google Sheets
- Works the same in Google Sheets.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])
| Argument | Required? | What it is |
|---|---|---|
text | Required | The text or cell to change. |
old_text | Required | The text to find. The match is case-sensitive. |
new_text | Required | What to put in its place. Use "" to delete it. |
instance_num | Optional | Which occurrence to replace. Leave it out to replace all of them. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","")," ",""),"-","") | 6125550148 | Nested to strip four kinds of characters |
| D3 | =SUBSTITUTE(B3,"2025","2026") | Gadget 2026 edition | An old model year swapped for the new one |
| D4 | =SUBSTITUTE(C4,"/"," > ",2) | hr/2025 > q4/review | Only the second slash is replaced |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Phone | Product | Path | Result |
| (612) 555-0148 | Widget Pro 2025 | sales/2026/q1/report | 6125550148 |
| 763-555-0193 | Gadget 2025 edition | ops/2026/q2/plan | Gadget 2026 edition |
| 952.555.0107 | Gizmo Mini | hr/2025/q4/review | hr/2025 > q4/review |
Practice SUBSTITUTE
From lesson 15, Cleaning messy data. Graded instantly, then re-checked on changed data.
Digits only
F2, remove the dashes from each phone number. Fill down to F6.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| Nothing gets replaced | SUBSTITUTE is case-sensitive, so "widget" won't match "Widget". Match the case exactly, or wrap the text in LOWER first. |
| The result won't add up | SUBSTITUTE returns text even when it looks like a number. Convert it: =VALUE(SUBSTITUTE(A2,"$","")). |
| You know the position, not the text | If the characters change but their position doesn't, use REPLACE instead. |
Tips
- Count how often something appears: =LEN(A2)-LEN(SUBSTITUTE(A2,"-","")) counts the dashes.
Questions
What is the difference between SUBSTITUTE and REPLACE?
SUBSTITUTE finds text by content, such as "replace every dash". REPLACE works by position, such as "replace 3 characters starting at position 1".
How do I remove a character from a cell?
Substitute it with nothing: =SUBSTITUTE(A2,"-",""). Nest SUBSTITUTE to remove several different characters.
Lessons that use SUBSTITUTE
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.