The SUBSTITUTE function in Excel replaces specific text within a text string with new text. It is especially useful when you need to clean data, standardize values, remove unwanted characters, or replace repeated text without manually editing each cell.
Unlike the REPLACE function, which works based on character positions, SUBSTITUTE works by finding matching text and replacing it. This makes it ideal for data cleaning and text manipulation tasks commonly used in business reporting, customer lists, inventory data, and imported datasets.
In This Guide, You’ll Learn
- What the SUBSTITUTE function does
- How to write the SUBSTITUTE syntax correctly
- How to replace text within a cell
- How to replace specific occurrences of text
- Common mistakes when using SUBSTITUTE
- Real-world business uses for data cleaning and standardization
What is the SUBSTITUTE Function?
The SUBSTITUTE function searches for text within a cell and replaces it with different text. You can replace all occurrences of a word or character, or you can choose to replace only a specific occurrence.
SUBSTITUTE Function Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])Argument Definitions
- text – The original text string containing the text you want to replace.
- old_text – The text you want Excel to find.
- new_text – The replacement text.
- instance_num (optional) – Specifies which occurrence to replace. If omitted, Excel replaces all occurrences.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> SUBSTITUTE Function Data
Replace a Word in Text
Suppose you want to replace the word “East” with “West” in a sales region description.
Formulas Used
=SUBSTITUTE(A2,"East","West")
How This Formula Works
- Excel searches for the text “East” and replaces it with “West”
Remove Characters from Text
You can remove characters by replacing them with an empty text string.
Formulas Used
=SUBSTITUTE(A2,"-","")
How This Formula Works
- Excel searches for the character dash (-) and removes it.
This is a common technique for cleaning imported data.
Replace All Occurrences
Suppose a customer’s name contains multiple extra spaces represented by periods.
Formulas Used
=SUBSTITUTE(A2,"."," ")=TRIM(SUBSTITUTE(A3,"."," "))
How This Formula Works
- SUBSTITUTE(A2,”.”,” “) converts the periods into spaces, leaving 3 spaces between names after the periods are removed
- TRIM(…) removes extra spaces and leaves only a single space between words.
This is a very common real-world data cleaning pattern:
Replace Only the Second Occurrence
Sometimes you only want to replace a specific occurrence.
Formulas Used
=SUBSTITUTE(A2,"-","/",2)
How This Formula Works
- The number 2 tells Excel to replace only the second dash (-)
- The first and third dashes remain unchanged
Common SUBSTITUTE Mistakes
Forgetting Quotation Marks
Incorrect:
=SUBSTITUTE(A2,East,West)Correct:
=SUBSTITUTE(A2,"East","West")Text values must always be enclosed in quotation marks.
Using SUBSTITUTE Instead of REPLACE
SUBSTITUTE searches for matching text.
REPLACE changes characters based on position numbers.
Choose the function that matches your objective.
Expecting Case-Insensitive Replacements
SUBSTITUTE is case-sensitive.
When to Use SUBSTITUTE vs REPLACE
| Function | Best Used For |
| SUBSTITUTE | Replacing specific text values |
| REPLACE | Replacing characters based on position numbers |
If you know the actual text you want to replace, SUBSTITUTE is usually the better choice.
If you only know the character position, use REPLACE.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> SUBSTITUTE Function Data
