🔤 Text Function · beginner
SUBSTITUTE Function in Google Sheets
Replaces specified text with new text, optionally only the Nth occurrence. Find-and-replace without regex.
Syntax
SUBSTITUTE(text, search_for, replace_with, [occurrence])Returns: text with replacements.
Excel equivalent: SUBSTITUTE (identical)
Parameters
| Name | Required | Description |
|---|---|---|
| text | Required | String to operate on. |
| search_for | Required | Substring to find. |
| replace_with | Required | Replacement text. |
| occurrence | Optional | Only Nth occurrence. Omitted = all. |
Examples
Remove spaces
=SUBSTITUTE(A2, " ", "")Normalize phone numbers.
Second occurrence only
=SUBSTITUTE(A2, "-", " ", 2)Only the second dash.
Chained
=SUBSTITUTE(SUBSTITUTE(A2, "$", ""), ",", "")Strip $ AND , for currency cleanup.
When to use an alternative
- REGEXREPLACE — Pattern-based.
- REPLACE — Replace by position.
Common errors and how to fix them
Case-sensitive surprise
Cause: SUBSTITUTE IS case-sensitive.
Fix: Use REGEXREPLACE with (?i).
Related functions
REGEXREPLACE
Replaces text matching a regex pattern with a substitution. Regex version of SUBSTITUTE....
FIND
Returns position of one string in another, case-sensitively....
SEARCH
Returns position of one string in another, case-insensitively. Supports * and ? wildcards....
CONCATENATE
Joins multiple text values into one string. The classic way to combine fields, but TEXTJOIN is usual...
LEFT
Returns the leftmost N characters of a string. The simplest text-extraction function — useful for pr...
Frequently Asked Questions
Case-sensitive?
Yes — rare case-sensitive function in Sheets. "Apple" ≠ "apple".
vs REPLACE?
SUBSTITUTE finds text. REPLACE removes by position.