Replace text with SUBSTITUTE
SUBSTITUTE swaps one piece of text for another wherever it appears in a cell, matching the exact characters you give it.
=SUBSTITUTE(A2,"-","")The sheet: Column A holds phone numbers written as 555-123-4567. You want the same numbers without the hyphens.
How it works, step by step
- 01A2 is the text to change.
- 02"-" is the text to find.
- 03"" is the replacement. An empty string removes the hyphen.
- 04Every hyphen in the cell is replaced unless you add a fourth argument to pick one occurrence.
The same logic in four tools
=SUBSTITUTE(A2,"-","")=SUBSTITUTE(A2,"-","")SELECT REPLACE(phone, '-', '') AS phone_clean
FROM contacts;df["phone_clean"] = df["phone"].str.replace("-", "", regex=False)Mistakes that break it
- !SUBSTITUTE is case sensitive: "a" does not match "A".
- !It does not accept wildcards or patterns. Use REGEXREPLACE in Google Sheets for patterns.
- !REPLACE is a different function that works by position, not by matching text.
- !Chain several SUBSTITUTE calls to remove different characters, for example parentheses and spaces.
Common questions
- How do I replace only the second occurrence?
- Add a fourth argument: =SUBSTITUTE(A2,"-","/",2).
- Will the result be a number?
- No, it stays text. Wrap it in VALUE if you need a number.
Got a version of this with your own ranges?
Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.