SUBSTITUTE Function

Summary

The Excel SUBSTITUTE function replaces specific text inside a string. You tell Excel what text to find and what to replace it with, and the function returns the updated result.

That makes SUBSTITUTE different from REPLACE. REPLACE works by position, while SUBSTITUTE works by matching text content. If you know the word, symbol, or substring you want to change, SUBSTITUTE is often the better tool.

SUBSTITUTE is especially helpful in cleanup work because it targets the actual text you want to change instead of relying on a fixed position. That makes it useful for removing symbols, normalizing separators, or replacing only one chosen occurrence when the rest of the text should stay untouched.

Syntax

=SUBSTITUTE(text, old_text, new_text, [instance_num])

text is the source string. old_text is what you want to find. new_text is what should replace it. instance_num is optional and lets you replace only one matching occurrence instead of all of them.

Arguments

• text - [Required] The original text string or cell reference.
• old_text - [Required] The text you want to replace. SUBSTITUTE is case-sensitive.
• new_text - [Required] The replacement text. Use "" to remove the matched text.
• instance_num - [Optional] The matching occurrence to replace. Omit it to replace all occurrences.

SUBSTITUTE vs Other Functions

Use SUBSTITUTE when you know what text should change. Use REPLACE when you know where the change should happen.

Using SUBSTITUTE

SUBSTITUTE is useful when the same word, symbol, or separator appears in many rows and needs to be changed consistently. Because it works by content, you do not have to calculate character positions first.

It is also useful for cleaning text. If you replace a character with an empty string, that character is removed. This is a common pattern for stripping spaces, hyphens, or other unwanted symbols.

• Use SUBSTITUTE when you know the exact text that should change.
• Use the 4th argument when only one occurrence should be replaced.
• Use "" as the replacement text when you want to remove something entirely.

Example 1 - Updating a Year Across a Label

If the old year is known, SUBSTITUTE can replace it directly without needing to know where it sits in the string.

=SUBSTITUTE(A2, "2021", "2024")
// "Excel-2021" -> "Excel-2024"

Example 2 - Replacing Only a Specific Occurrence

The optional instance_num argument is useful when the same character appears several times but only one match should change. If you omit it, all matches are replaced.

=SUBSTITUTE(A3, "-", "B", 2)
// "A-A-A" -> "A-BA"

Example 3 - Removing a Character Entirely

If the replacement text is empty, SUBSTITUTE removes the matched text. This is a common way to strip unwanted symbols from imported values.

=SUBSTITUTE(A2, "-", "")
// "Excel-2021" -> "Excel2021"

Example 4 - Swapping a Separator Character

SUBSTITUTE can also convert one separator style into another. This is useful when data needs to be prepared for a different format or system.

=SUBSTITUTE(A2, "-", "/")
// "Excel-2021" -> "Excel/2021"

SUBSTITUTE is case-sensitive. If you search for "excel", it will not match "Excel". If the source data uses inconsistent capitalization, normalize it first or use another approach.

Conclusion Recap

SUBSTITUTE is useful when you know exactly which word, character, or symbol needs to change. In this lesson, it handled label updates, one specific match, full removal, and separator changes without needing position numbers.

That makes it a handy cleanup tool for everyday text work. Just remember that it matches text directly and it is case-sensitive, so Excel only changes what truly matches your old_text.

• Summary: SUBSTITUTE replaces text by matching content.
• Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num]).
• Key point: SUBSTITUTE is case-sensitive and replaces all matches unless you target one occurrence.
• Practical usage: Cleaning text, updating labels, removing symbols, and changing separators.
• Best pattern: Use an empty replacement string when you want to remove matched text.