SUBSTITUTE in Google Sheets
Use SUBSTITUTE to replace specific text inside a string — swap a word, strip a character, or clean up messy data without touching the rest.
=SUBSTITUTE(A2, "-", "")
Removes every hyphen from A2 by replacing it with an empty string.
How it works
SUBSTITUTE takes the text, the old text to find, the new text to put in its place, and an optional occurrence number if you only want to replace one specific instance. It matches exact text and is case-sensitive. Leave the new text empty to delete the old text. To swap by position rather than by content, use REPLACE instead.
Variations
Replace a word
=SUBSTITUTE(A2, "Street", "St")
Swaps every "Street" for "St".
Replace only the 2nd occurrence
=SUBSTITUTE(A2, "-", "/", 2)
The fourth argument targets a single occurrence — here the second hyphen.
Strip spaces entirely
=SUBSTITUTE(A2, " ", "")
Removes all spaces; use TRIM instead to only collapse extra ones.
Nested for two replacements
=SUBSTITUTE(SUBSTITUTE(A2, ",", ""), "$", "")
Chain SUBSTITUTE calls to clean several characters at once.
Examples
| Scenario | Formula |
|---|---|
| Remove dashes from a code | =SUBSTITUTE(A2, "-", "") |
| Currency text to a number | =VALUE(SUBSTITUTE(A2, "$", "")) |
| Fix a typo everywhere | =SUBSTITUTE(A2, "teh", "the") |
FAQ
What's the difference between SUBSTITUTE and REPLACE?
SUBSTITUTE swaps text by matching its content; REPLACE swaps characters by their position and length in the string.
Is SUBSTITUTE case-sensitive?
Yes. "cat" and "Cat" are treated as different text, so match the exact casing you want to replace.
How do I replace only one occurrence?
Add the occurrence number as a fourth argument, e.g. SUBSTITUTE(A2, "-", "/", 2) changes only the second hyphen.