MS Excel: SUBSTITUTE function to replace text string

The SUBSTITUTE function in Excel is used to replace existing text with new text in a string, allowing you to target specific occurrences of a substring. Unlike REPLACE, which works based on position, SUBSTITUTE identifies exact text to change—making it ideal for cleaning and restructuring textual data.

Syntax

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

Arguments:

Argument	Description
text	Required. The full string or cell containing the text.
old_text	Required. The substring you want to replace.
new_text	Required. The text you want to replace it with.
instance_num	Optional. Specifies which occurrence to replace (if omitted, replaces all).

Returns: A string where occurrences of old_text are replaced by new_text.

Examples with Tables

Example 1: Replace All Occurrences

=SUBSTITUTE("apples and apples", "apples", "oranges")

Formula	Result
=SUBSTITUTE("apples and apples", "apples", "oranges")	oranges and oranges

Example 2: Replace Only the First Occurrence

=SUBSTITUTE("apples and apples", "apples", "oranges", 1)

Formula	Result
=SUBSTITUTE("apples and apples", "apples", "oranges", 1)	oranges and apples

Example 3: Use in a Cell Reference

Assume cell A1 = "cat bat rat cat"

=SUBSTITUTE(A1, "cat", "dog")

A1	Formula	Result
cat bat rat cat	=SUBSTITUTE(A1, "cat", "dog")	dog bat rat dog

Example 4: Remove Characters

You can remove characters by replacing them with an empty string:

=SUBSTITUTE("123-456-789", "-", "")

Formula	Result
=SUBSTITUTE("123-456-789", "-", "")	123456789

Use Cases

Scenario	How SUBSTITUTE Helps
Cleaning up imported data	Remove or fix unwanted characters
Changing label names	Replace certain words or labels
Partial text modification	Update or localize dynamic text in formulas
Fixing case-sensitive duplicates	Combine with UPPER, LOWER, or PROPER

SUBSTITUTE vs REPLACE

Feature	SUBSTITUTE	REPLACE
Replaces by...	Matching text	Character position
Replace one or all	Specific instance or all	Only one range per formula
Use case	More dynamic, pattern-based changes	Positional or fixed-length edits

Notes

• SUBSTITUTE is case-sensitive: "A" and "a" are treated as different characters.

• If old_text is not found, the original text is returned unchanged.

• For non-case-sensitive substitution, use LOWER or UPPER on both the source and search text.

Combine with Other Functions

Task	Formula Example
Remove all spaces	=SUBSTITUTE(A1, " ", "")
Replace commas with newlines	=SUBSTITUTE(A1, ",", CHAR(10)) (enable wrap text)
Case-insensitive substitute	=SUBSTITUTE(LOWER(A1), "apple", "orange")

Related Functions

Function	Description
REPLACE	Replaces part of a string based on position
TEXT	Formats values as text
LEFT/RIGHT	Extracts characters from a string
FIND/SEARCH	Finds position of text in a string
TRIM	Removes extra spaces

Summary

Feature	Description
Function Name	SUBSTITUTE
Purpose	Replace specific text in a string
Replace All	✅ Default behavior
Replace One	✅ Use instance_num parameter
Case-Sensitive	✅ Yes

Final Thoughts

The SUBSTITUTE function is a powerful tool for text transformation in Excel. Whether you're cleaning imported data, editing values in bulk, or manipulating strings dynamically, SUBSTITUTE allows you to do it cleanly and efficiently—especially when combined with other text functions.