How to Use SUBSTITUTE in Excel: Replace Text Easily

The SUBSTITUTE function in Excel replaces specific text in a cell with new text. You provide the original text, the text to replace, and the replacement text, and Excel swaps them wherever found.

Syntax

The SUBSTITUTE function has this form:

• text: The original text or cell reference.
• old_text: The text you want to replace.
• new_text: The text to replace with.
• instance_num (optional): Which occurrence to replace. If omitted, all occurrences are replaced.

excel

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

Example

This example replaces the word "cat" with "dog" in cell A1. If A1 contains "The cat sat on the cat mat", the formula changes all "cat" words to "dog".

excel

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

Output

The dog sat on the dog mat

Common Pitfalls

Common mistakes include:

• Not using quotes around text values.
• Forgetting that SUBSTITUTE is case-sensitive.
• Not specifying instance_num when you want to replace only one occurrence.

Example of replacing only the second occurrence of "cat":

excel

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

Output

The cat sat on the dog mat

Quick Reference

Parameter	Description
text	Original text or cell reference
old_text	Text to find and replace
new_text	Text to replace with
instance_num (optional)	Which occurrence to replace; all if omitted

Key Takeaways

Use SUBSTITUTE to replace specific text inside a cell without changing the whole content.

Remember SUBSTITUTE is case-sensitive and requires text values in quotes.

Specify the instance number to replace only one occurrence instead of all.

SUBSTITUTE works well for cleaning or updating text data quickly.