SUBSTITUTE function

The SUBSTITUTE function in Excel allows you to replace specific text within a cell’s contents with new text. It’s a handy tool for making changes to your data without manually editing each cell.

Syntax

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

Arguments

old_textThe original text containing the text you want to replace.
find_textThe text you want to find and replace within the old_text.
new_textThe new text that will replace the find_text in the old_text.
[instance_num](Optional) The instance number (default is 1) specifying which occurrence of the find_text to replace in old_text. If omitted, all occurrences are replaced.

How to use

The SUBSTITUTE function is simple to use. Let’s go through some examples:

This formula replaces the first occurrence of “World” with “Universe” in the text “Hello, World!” resulting in “Hello, Universe!”.

This formula replaces all occurrences of “apples” with “bananas” in the text, giving you “bananas are red, bananas are green, bananas are delicious.”

Here, we specify that we want to replace the second occurrence of “green” with “purple,” resulting in “Blue and yellow make purple.”

Remember that the SUBSTITUTE function is not case-sensitive, so it will replace text regardless of letter case.

If you encounter a term you’re not familiar with, such as “function” or “formula,” you can find definitions and additional information on Excel functions on Microsoft’s Excel support site.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.