SUBSTITUTE & TEXT in Google Sheets
SUBSTITUTE(text, search_for, replace_with) replaces every occurrence of one piece of text with another: =SUBSTITUTE(A2, "-", " ") turns dashes into spaces, and replacing with "" deletes them. Add a fourth argument to replace only one occurrence.
TEXT(value, format) turns a number or date into formatted text. It matters when you join values into a sentence, because & drops number formatting: ="Total: "&TEXT(C2, "$#,##0.00") or ="Due "&TEXT(B2, "mmm d").
Syntax
=SUBSTITUTE(text, search_for, replace_with) · =TEXT(number, format)
Example
=A2&": "&TEXT(C2, "$0.00")
Builds a label like "TS-RED-M: $19.50".
Common mistakes
- SUBSTITUTE is case-sensitive: "a" won't replace "A".
- TEXT returns text, so the result can't be summed.
- Joining a price without TEXT shows 19.5 instead of $19.50.
← Cleaning text: TRIM & PROPER · SPLIT → · All lessons