Excel SUBSTITUTE: Replace Text in Cells
By Szabó Gergő · Updated
SUBSTITUTE replaces specific text with other text. Unlike REPLACE, it finds the old string rather than a character position, which is better for cleaning imported data.
Syntax and arguments
=SUBSTITUTE(text, old_text, new_text, [instance_num])- text
- The source string or cell.
- old_text
- The text to find.
- new_text
- The replacement text, which can be empty to delete.
- instance_num
- Optional occurrence to replace; omit to replace all.
SUBSTITUTE examples
Imported amount in A2 is 1,234.50 as text.
=VALUE(SUBSTITUTE(A2,",",""))Commas are stripped, then VALUE turns the result into a number.
A2 is NY-1044-A.
=SUBSTITUTE(A2,"-"," ",1)instance_num 1 changes the first hyphen only.
A2 has line breaks and extra spaces.
=TRIM(SUBSTITUTE(A2,CHAR(10)," "))Line breaks become spaces, then TRIM collapses repeats.
Common mistakes
Case does not match
SUBSTITUTE is case-sensitive. Normalize with UPPER or LOWER first if needed.
Using REPLACE when you do not know the position
REPLACE needs start_num. Prefer SUBSTITUTE for known snippets.
Breaking numbers with leftover currency symbols
Strip $ and spaces before VALUE, or use a locale-aware parse.
SUBSTITUTE FAQ
How do I delete a character?
Set new_text to "" such as =SUBSTITUTE(A2,"*","").
What is the difference versus REPLACE?
REPLACE uses a starting position and length. SUBSTITUTE uses the old text itself.
Can I replace across a whole column?
Yes. Fill or spill the formula, or use Find and Replace for a one-time static cleanup.