SheetsdrillSUBSTITUTE
SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number]) replaces every occurrence of a piece of text with new text, or only the nth one when you give an occurrence number. Use an empty replacement to delete characters, and REGEXREPLACE when the thing to remove is a pattern rather than fixed text.
Syntax
=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])| Part | What it is |
|---|---|
text_to_search | The cell or string to change. |
search_for | The exact text to find; case sensitive. |
replace_with | The replacement, or "" to remove. |
occurrence_number | Optional. Replace only this occurrence (1 for the first); all when omitted. |
Examples
=SUBSTITUTE("555-123-4567", "-", "")Result: 5551234567
=VALUE(SUBSTITUTE("$1,250.00", "$", ""))Result: 1250
VALUE handles the thousands separator; SUBSTITUTE strips the symbol.
=SUBSTITUTE("a-b-c", "-", "/", 2)Result: a-b/c
Common mistakes
- Nothing changes because SUBSTITUTE is case sensitive; abc does not match ABC. Use REGEXREPLACE with (?i) for case-insensitive replacement.
- The result is text, so numbers no longer add up; wrap in VALUE.
- Confusing SUBSTITUTE with REPLACE: REPLACE works by position (start, length), SUBSTITUTE by matching text.
Practice SUBSTITUTE
These drills check your answer on live data, free, no account needed.
- Digits onlyPhone numbers were typed in different styles. Remove everything that is not a digit, keeping the result as text. Fill B2 and B3 using REGEXREPLACE.
Questions
How do I replace multiple characters at once?
Nest them: =SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", ""), or use REGEXREPLACE with a character class.
Is there a find and replace for the whole sheet?
Yes, Edit > Find and replace (Ctrl+H) changes cells in place; SUBSTITUTE is for formulas that keep the original.
Related
- REGEXMATCH, REGEXEXTRACT and REGEXREPLACEHow the regex functions in Google Sheets work: test a pattern with REGEXMATCH, pull text out with REGEXEXTRACT and clean it with REGEXREPLACE, with examples.
- TRIMHow TRIM in Google Sheets removes leading, trailing and repeated spaces, how to clean a whole column, and how to clear non-breaking spaces and line breaks.
- CONCATENATE and &How to combine cells in Google Sheets with CONCATENATE, CONCAT and the & operator, add spaces and separators, keep number and date formats, avoid mistakes.
- TEXTHow the TEXT function in Google Sheets turns a number or date into formatted text: month labels, currency, percentages and leading zeros, with common codes.