SUBSTITUTE replaces matching text inside a string. It returns a new text result without editing the original cell. Omit the optional occurrence number to replace every match.
SUBSTITUTE function syntax
=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])- text_to_search: the source cell or quoted text.
- search_for: the literal, case-sensitive text to find.
- replace_with: the replacement text; use “” to remove matches.
- occurrence_number: optionally choose one match, starting at 1.
Set up the example data
Enter this dataset starting in A1. The first row contains headings. Keep the result area separate from the source table.
| Text |
|---|
| red-red-red |
| ITEM-101 |
| ITEM-ITEM-9 |
| New York |
| new york |
Replace every matching occurrence
Enter this formula in A9. All three lowercase red matches become blue. The source text in A2 is unchanged, so you can compare the original and cleaned values.
=SUBSTITUTE(A2,"red","blue")Result: blue-blue-blue.
Replace only the second occurrence
The optional fourth argument targets one occurrence. Here, only the second red changes. An occurrence number beyond the available matches leaves the original text unchanged.
=SUBSTITUTE(A2,"red","blue",2)Result: red-blue-red.
Remove a repeated identifier string
Replacing ITEM- with empty text removes both matches from A4, returning 9. This is literal substring removal, not a rule that only removes a prefix.
=SUBSTITUTE(A4,"ITEM-","")Result: 9.
Remove only a leading prefix
REGEXREPLACE uses ^ to anchor ITEM- at the start. The result keeps the later ITEM- segment. Use REPLACE instead when the task is defined by a fixed character position.
=REGEXREPLACE(A4,"^ITEM-","")Result: ITEM-9.
Standardize a case-sensitive label
This replaces the lowercase phrase in A6. It would not match New York in A5 because SUBSTITUTE matches case. Check your source variations before applying a standardized label.
=SUBSTITUTE(A6,"new york","New York")Result: New York.
Remove several punctuation characters
Nested calls remove the opening parenthesis, closing parenthesis, space and hyphen. The illustrative phone string becomes digits as text, preserving the possibility of leading zeros.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("(212) 555-0100","(",""),")","")," ",""),"-","")Result: 2125550100.
Convert cleaned numeric text into a number
SUBSTITUTE returns text. VALUE converts the cleaned 1234 string to a number for arithmetic. Do not convert identifiers or phone numbers when leading zeros are significant.
=VALUE(SUBSTITUTE("1,234",",",""))Result: 1234.
Other Google Sheets articles you may also like