What SUBSTITUTE Does
SUBSTITUTE replaces specific text within a string with new text. Unlike Find & Replace (Ctrl+H), it works inside a formula so you can transform data without modifying the original cells. It is case-sensitive, which gives you precise control over what gets changed.
Syntax
=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
| Parameter | Description |
|---|---|
| text_to_search | The text or cell reference containing the text. |
| search_for | The text string to find. Case-sensitive. |
| replace_with | The text to replace it with. |
| occurrence_number | Which occurrence to replace. If omitted, all occurrences are replaced. |
Basic Examples
Example 1: Replace a Word
| A | B (Formula) | B (Result) | |
|---|---|---|---|
| 1 | Status | Cleaned | Cleaned |
| 2 | In Progress | =SUBSTITUTE(A2, "In Progress", "Active") | Active |
| 3 | In Progress - Review | =SUBSTITUTE(A3, "In Progress", "Active") | Active - Review |
Example 2: Remove Unwanted Characters
Strip dashes from phone numbers:
=SUBSTITUTE(A2, "-", "")
If A2 is "555-123-4567", the result is 5551234567.
Example 3: Replace Only a Specific Occurrence
Replace only the second comma in a string:
=SUBSTITUTE("a,b,c,d", ",", " |", 2)
Result: a,b |c,d — only the second comma is replaced.
Advanced Examples
Chained SUBSTITUTE for Multiple Replacements
Clean messy CRM data by removing multiple unwanted characters:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", ""), " ", "")
If A2 is "(555) 123 4567", the result is 5551234567. Each SUBSTITUTE handles one character.
Extract Domain from Email
Combine SUBSTITUTE with MID and FIND, or use this simpler approach to isolate everything after the @ sign:
=SUBSTITUTE(A2, LEFT(A2, FIND("@", A2)), "")
If A2 is "maria@acme.com", this removes everything up to and including "@", leaving acme.com.
Common Mistakes
- Forgetting it's case-sensitive.
=SUBSTITUTE(A2, "usa", "US")will not match "USA" or "Usa". If you need case-insensitive replacement, use REGEXREPLACE:=REGEXREPLACE(A2, "(?i)usa", "US"). - Replacing partial matches unintentionally.
=SUBSTITUTE(A2, "an", "AN")changes "Canada" to "CAN ada" and "France" to "FrANce". Make sure your search string is specific enough. - Not using the occurrence parameter when needed. Without it, SUBSTITUTE replaces every match. If your text has "the" multiple times and you only want the first one changed, specify
1as the fourth argument.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English — like "replace all dashes with spaces in the phone column" — and the formula is generated for you. Install SheetAI to try it free.