Find and Replace Text With SUBSTITUTE

Excel & Google Sheets

How it works

SUBSTITUTE scans the text in A2 for every occurrence of the first string and swaps in the second — replacing a dash with nothing simply deletes all the dashes. Because it's a formula, the cleanup re-runs itself whenever fresh data is pasted in, which is what makes it better than one-off Find & Replace for recurring exports. An optional fourth argument targets a single occurrence: =SUBSTITUTE(A2,"-","/",2) changes only the second dash and leaves the rest alone. It matches by text, unlike its sibling REPLACE, which overwrites by character position — use REPLACE when you know where, SUBSTITUTE when you know what. And you can nest one inside another to make several swaps in one cell: =SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","") strips both the currency symbol and the thousands comma.

A2

The cell holding the text to clean.

"-"

The text to find — case-sensitive, and it can be more than one character.

""

The replacement. Empty text deletes every match; add a 4th argument like 2 to replace only the 2nd occurrence.

When to use it

Use it to strip dashes or spaces out of phone numbers and SKUs before matching them, swap separators in product codes, or remove currency symbols and thousands commas from amounts stored as text.

Common mistakes

• ✕Expecting it to ignore case.

  ✓SUBSTITUTE is case-sensitive: =SUBSTITUTE(A2,"usd","") leaves "USD" untouched. Match the exact casing, or normalize the text first with UPPER or LOWER.

• ✕Reaching for SUBSTITUTE when you mean REPLACE.

  ✓SUBSTITUTE finds text; REPLACE overwrites a position. To rewrite the first 3 characters whatever they are, use =REPLACE(A2,1,3,"+1"). To change every dash wherever it appears, use SUBSTITUTE.

• ✕Treating the cleaned result as a number.

  ✓SUBSTITUTE always returns text — SUM quietly skips a text "1250". Convert it: =VALUE(SUBSTITUTE(A2,"-","")) or multiply the result by 1.