Excel SUBSTITUTE Function
SUBSTITUTE replaces matching text inside a text value.
Use it to clean labels, swap separators, remove known words, normalize IDs, or replace only one occurrence of repeated text.
SUBSTITUTE syntax & arguments
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])
-
1
text
RequiredThe text value, cell reference, or formula result where replacements should happen.
-
2
old_text
RequiredThe exact text to find inside
text. SUBSTITUTE is case-sensitive. -
3
new_text
RequiredThe text that should replace each matched
old_textvalue. -
4
instance_num
OptionalWhich occurrence of
old_textto replace. If omitted, SUBSTITUTE replaces every occurrence.
Example
=SUBSTITUTE(A2, "-", " ")
Replace every hyphen in A2 with a space.
SUBSTITUTE caveats
-
It is case-sensitive
"ID"and"id"are different matches. SUBSTITUTE only replaces text that matches the case you provide. -
Omitting instance_num replaces every match
Use
instance_numonly when one specific occurrence should change and other matches should remain. -
Spaces are part of the match
old_textmust match every character, including leading, trailing, or repeated spaces.
Cleaning messy labels? Use TRIM after SUBSTITUTE when the replacement can leave extra spaces behind.
Intro SUBSTITUTE practice problems
No intro SUBSTITUTE problems are currently available.
Advanced SUBSTITUTE practice problems
Use SUBSTITUTE alongside other Excel functions in realistic, less-prescriptive challenges.
