Excel function guide · Text
SUBSTITUTE function in Excel
SUBSTITUTE replaces specific text inside a value with other text, for example every hyphen in a phone number with nothing. By default it replaces every occurrence; an optional fourth argument replaces only one.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])
| Argument | Required? | What it means |
|---|---|---|
| text | Required | The text or cell to change. |
| old_text | Required | The text to find. Case-sensitive. |
| new_text | Required | What to put in its place. Use "" to delete it. |
| instance_num | Optional | Which occurrence to replace (1 = the first). Left out, every occurrence is replaced. |
SUBSTITUTE examples
Every example uses this small table. The results are what Excel shows in the cell.
| A | B | |
|---|---|---|
| 1 | Phone | Product code |
| 2 | 020-7946-0018 | lamp_desk_small |
| 3 | 0161-496-0753 | chair_office_mesh |
| 4 | 0113-496-0021 | arm_monitor_dual |
1. Remove a character
=SUBSTITUTE(A2, "-", "")
Result: 02079460018
Every hyphen is replaced with nothing. The result is text, so the leading 0 survives.
2. Swap one character for another
=SUBSTITUTE(B2, "_", " ")
Result: lamp desk small
Underscores become spaces.
3. Only one occurrence
=SUBSTITUTE(A3, "-", " ", 1)
Result: 0161 496-0753
instance_num 1 replaces only the first hyphen.
Try it now
Practise SUBSTITUTE in a live sheet.
Type each formula yourself. Answers are checked by the same engine as the full course. No account needed.
Step 1 of 3
In C2, remove every hyphen from the phone number in A2.
Common SUBSTITUTE mistakes
It's case-sensitive
SUBSTITUTE(A2, "lamp", "Lamp") won't touch "LAMP". Normalise case first with LOWER or UPPER if the data is inconsistent.
The result is text
=SUBSTITUTE("1,250", ",", "") returns the text "1250". Wrap it in VALUE to get a number you can add up.
SUBSTITUTE versus REPLACE
SUBSTITUTE swaps text you name; Excel's REPLACE swaps characters at a position. Use SUBSTITUTE when you know what to change but not where.
Several replacements
Nest one inside another: SUBSTITUTE(SUBSTITUTE(A2, "-", ""), " ", "") removes both hyphens and spaces.
Practise this in Excel Quest
FIND, SEARCH & SUBSTITUTE Members
Course 7 · Text & Data Cleaning · 14 min
Split “Last, First” names, pull email domains, strip phone formatting and flag urgent notes.
This lesson is included in Formula Quest memberships. A free account starts you on the free lessons of the Excel Quest curriculum.