How to Use the SUBSTITUTE Function to Replace Text in Microsoft Excel

When cleaning up messy data in Microsoft Excel, you often need to find a specific word or character string and replace it with something else. While the standard “Find and Replace” (Ctrl+H) tool is useful for one-off manual edits, it is useless if you need to build dynamic, automated formulas. If you want to automatically swap out text within a formula while keeping the original data perfectly intact, you must use the powerful SUBSTITUTE function.

How the SUBSTITUTE Function Works

Unlike the REPLACE function, which requires you to know the exact mathematical starting position of the text you want to remove (e.g., “start at character 5”), the SUBSTITUTE function simply searches for the text string itself, no matter where it is located in the cell.

The syntax for the formula is: =SUBSTITUTE(text, old_text, new_text, [instance_num])

• text: The cell containing the original data you want to modify (e.g., A2).
• old_text: The exact word, character, or phrase you want to remove (must be wrapped in quotation marks).
• new_text: The exact word, character, or phrase you want to insert in its place (must be wrapped in quotation marks).
• [instance_num]: This is an optional argument. If the old_text appears three times in the cell, you can type 1 to only replace the first occurrence. If you leave this blank, Excel will replace every occurrence it finds.

Common Use Cases for Data Cleaning

Fixing Regional Spelling

Imagine you have a column of product descriptions written in British English (e.g., “The colour of the aluminium casing”), and you need to translate them to American English for a US-based catalog. You can use SUBSTITUTE to automatically swap the spelling.

=SUBSTITUTE(A2, "colour", "color")

If the word “colour” appears anywhere in cell A2, the formula will instantly output a new string featuring the American spelling.

Removing Unwanted Characters

Data imported from legacy software systems often contains unwanted formatting characters, such as hyphens or asterisks inside what should be a clean ID number (e.g., “ID-459-B”). You can use the function to completely strip these characters by setting the new_text argument to an empty string (two quotation marks with absolutely nothing between them).

=SUBSTITUTE(A2, "-", "")

This formula will hunt down every single hyphen in cell A2 and replace it with nothing, outputting a clean, solid string of text (e.g., “ID459B”). Because this is a formula, you can drag it down an entire column of 5,000 messy ID numbers, and it will instantly clean all of them in a fraction of a second.