SUBSTITUTE - Replace known text, all or one occurrence
Normalize phone separators, update a code prefix, or replace an email domain without treating text identifiers as numbers.
By the end, you can
- Replace matching text with a chosen string.
- Select one occurrence or all occurrences.
- Account for case sensitivity and leading zeroes.
Before you start: Understand text literals and TRIM for ordinary space cleanup.
In this lesson
Replace a known string rather than a position
SUBSTITUTE changes occurrences of old_text inside a text value. It is useful for phone dashes, a known product-code prefix, or a legacy email domain. Unlike positional extraction, it finds a matching string; it does not infer which occurrence has business meaning. The source remains unchanged when the formula is in another cell.
Syntax and arguments
=SUBSTITUTE(text, old_text, new_text, [instance_num])Omit instance_num to replace every occurrence. Supply a positive occurrence number to replace just that one.
| Argument | Purpose | Example |
|---|---|---|
textRequired | Source text to inspect. | A2 |
old_textRequired | The case-sensitive string to replace. | B2 |
new_textRequired | Replacement text; an empty string deletes the match. | C2 |
instance_numOptional | The 1-based occurrence to replace; omitted means all. | 2 |
Three targeted import fixes
011 555 1020| A | B | C | D | |
|---|---|---|---|---|
| 1 | Source | Old text | New text | Result |
| 2 | 011-555-1020 | - | 011 555 1020 | |
| 3 | DEL-DEL-1042 | DEL | MUM | MUM-MUM-1042 |
| 4 | anika@old.test | old.test | apex.test | anika@apex.test |
=SUBSTITUTE(A2,B2,C2)Replace every dash in the phone with an ordinary space. Other rows use their own old/new strings.
One occurrence, deletion, and case
=SUBSTITUTE(A3,"DEL","MUM",2)The first DEL remains, while the second occurrence changes.
=SUBSTITUTE(A2,"-","")Remove every dash while keeping the phone digits as text, including the leading zero.
=SUBSTITUTE(A4,"OLD.TEST","apex.test")Uppercase OLD.TEST does not match lowercase old.test, so the original string remains.
Distinctive behavior and compatibility
- Matching is case-sensitive. No occurrence of old_text means the original text is returned unchanged.
- An instance number beyond the number of matches leaves the text unchanged. A zero or negative instance number is invalid and gives #VALUE!.
- SUBSTITUTE does not interpret * or ? as wildcard patterns; it replaces their literal text if requested.
- It is available in older/current Excel. Replacing a known nonbreaking space with an ordinary one before TRIM is a useful Excel cleanup recipe, but it does not remove every unwanted character automatically.
Common mistakes and Practice scope
The linked phone task uses ASCII dashes and ordinary spaces. Its TRIM step follows the workspace's broader whitespace behavior; advanced CHAR-based cleanup recipes should be tried in Excel.
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.