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_text | The original text containing the text you want to replace. |
| find_text | The text you want to find and replace within the old_text. |
| new_text | The 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:
|
1 |
=SUBSTITUTE("Hello, World!", "World", "Universe") |
This formula replaces the first occurrence of “World” with “Universe” in the text “Hello, World!” resulting in “Hello, Universe!”.
|
1 |
=SUBSTITUTE("Apples are red, apples are green, apples are delicious.", "apples", "bananas") |
This formula replaces all occurrences of “apples” with “bananas” in the text, giving you “bananas are red, bananas are green, bananas are delicious.”
|
1 |
=SUBSTITUTE("Blue and yellow make green.", "green", "purple", 2) |
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.