Text
How to Use SUBSTITUTE in Excel (Find and Replace Text)
SUBSTITUTE swaps one piece of text for another inside a cell: changing "Mr." to "Ms.", turning dashes into spaces, or stripping out unwanted characters. It matches by content, so it finds your text wherever it appears.
You can replace every occurrence at once or target just the first, second, or nth one. This guide covers the syntax, worked examples, and how SUBSTITUTE differs from the position-based REPLACE function.
SUBSTITUTE syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num]) | Argument | Required | What it does |
|---|---|---|
text | Required | The cell or text you want to change. |
old_text | Required | The text to find and replace. This match is case-sensitive. |
new_text | Required | The text to put in its place. Use "" to delete the old text. |
instance_num | Optional | Which occurrence to replace (1 for the first, 2 for the second, and so on). Omit it to replace every occurrence. |
Note: SUBSTITUTE replaces by content and is case-sensitive. To replace by position instead (characters X to Y), use REPLACE. Works in Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Replace every occurrence
=SUBSTITUTE(A2, "-", "/") With no instance number, SUBSTITUTE swaps every dash for a slash. Great for reformatting codes and dates in bulk.
2. Remove a character entirely
=SUBSTITUTE(A2, ",", "") Replacing with an empty string "" deletes the text. This turns "1,250" into "1250" so it can be converted to a real number.
3. Replace only one occurrence
=SUBSTITUTE(A2, "-", " ", 2) The fourth argument, 2, limits the change to the second hyphen and leaves the others untouched.
4. Count how many times a word appears
=(LEN(A2)-LEN(SUBSTITUTE(A2, "error", "")))/LEN("error") Removing the word and comparing lengths reveals how many characters disappeared; dividing by the word length gives the count.
How to write SUBSTITUTE step by step
- 1
Click the result cell and type =SUBSTITUTE(
- 2
Click the cell with the text, then a comma.
- 3
Type the text to find in quotes, a comma, then the replacement text in quotes.
- 4
To delete the text instead, use "" as the replacement.
- 5
Optionally add a comma and a number to replace only that occurrence, then close the bracket ) and press Enter.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
Nothing gets replaced | old_text does not match because of a case difference or a hidden space. | SUBSTITUTE is case-sensitive, so match the exact casing; use TRIM or CLEAN first if stray spaces or characters are present. |
#VALUE! | instance_num is zero or negative. | Use a positive whole number, or omit it to replace every occurrence. |
Wrong occurrence changed | instance_num counted matches you did not expect. | Count occurrences from the left starting at 1, and check for earlier matches in the text. |
Replaced too much | old_text also appears inside other words. | Make old_text more specific (include surrounding spaces or punctuation) so it only matches the intended text. |
Frequently asked questions
What is the difference between SUBSTITUTE and REPLACE?
SUBSTITUTE replaces text by its content, wherever it appears, and is case-sensitive. REPLACE replaces text by position, swapping a set number of characters starting at a given spot. Use SUBSTITUTE when you know the text, REPLACE when you know the location.
Is SUBSTITUTE case-sensitive?
Yes. SUBSTITUTE only matches text with the same capitalization, so "cat" will not replace "Cat". Adjust the casing of old_text to match, or change the case first with UPPER or LOWER.
How do I remove a character with SUBSTITUTE?
Replace it with an empty string: =SUBSTITUTE(A2, ",", "") removes every comma. Nest several SUBSTITUTE calls to strip multiple characters at once.
How do I replace only the first occurrence?
Add 1 as the fourth argument: =SUBSTITUTE(A2, "-", " ", 1) changes only the first hyphen and leaves the rest in place.