SheetSense

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.

fx
=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

  1. 01A2 is the text to change.
  2. 02"-" is the text to find.
  3. 03"" is the replacement. An empty string removes the hyphen.
  4. 04Every hyphen in the cell is replaced unless you add a fourth argument to pick one occurrence.

The same logic in four tools

Excel
=SUBSTITUTE(A2,"-","")
Google Sheets
=SUBSTITUTE(A2,"-","")
SQL
SELECT REPLACE(phone, '-', '') AS phone_clean
FROM contacts;
Python (pandas)
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.