SUBSTITUTE replaces occurrences of old text with new text inside a string. It is case-sensitive and can target a specific occurrence when you supply the optional instance_num argument.
=SUBSTITUTE(text, old_text, new_text, [instance_num])What does SUBSTITUTE do?
SUBSTITUTE is a text function that swaps one piece of text for another inside a cell. You give it the original text, the text to find, and the replacement text, and it returns a new string with every match replaced. Unlike Find & Replace, SUBSTITUTE works inside a formula, so the change updates automatically when the source data changes.
This matters because real spreadsheets are full of inconsistent text: product codes with stray spaces, phone numbers with dashes, or labels that mix old and new department names. Cleaning that by hand is slow and error-prone. SUBSTITUTE lets you normalise text in one formula and reuse it across thousands of rows.
A typical situation: a sales report exports customer names as 'Last, First' and you need 'First Last' for a mail merge. With SUBSTITUTE you can remove the comma and rearrange the parts in a helper column, then copy the result as values. The same approach works for stripping currency symbols, fixing date separators, or replacing a specific occurrence of a word without touching the others.
When to use SUBSTITUTE
- Removing unwanted characters such as commas, spaces, or currency symbols from imported data.
- Standardising phone numbers or product codes by replacing dashes or dots with a consistent separator.
- Swapping old department or category names for new ones in a report.
- Changing only the second occurrence of a word while leaving the first intact.
- Cleaning text before using it in VLOOKUP or XLOOKUP so that keys match exactly.
SUBSTITUTE syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])| Argument | Required? | What to enter |
|---|---|---|
text | Required | The original text or a reference to the cell that contains it. Example: A2 or "North, Sales". |
old_text | Required | The text you want to find and replace. It is case-sensitive. Example: "," to find a comma. |
new_text | Required | The text that will replace old_text. Use an empty string "" to delete old_text. Example: " " to replace a comma with a space. |
instance_num | Optional | Which occurrence of old_text to replace. If omitted, every occurrence is replaced. Example: 2 replaces only the second match. |
What it returns: A text string with the specified occurrences of old_text replaced by new_text. The original cell is not changed.
SUBSTITUTE examples
1 Replace a comma with a space
The goal is to turn 'Last, First' into 'Last First'. The formula looks in cell A2 for a comma and replaces it with a space. Because instance_num is omitted, every comma in the cell is replaced. The result is a clean name that is easier to read or split later.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | |||
| 2 | Smith, John | Smith John | ||
| 3 | Doe, Jane | |||
| 4 | Brown, Mike | |||
| 5 | Taylor, Ann | |||
| 6 | Wilson, Tom | |||
| 7 |
2 Remove dashes from phone numbers
Phone numbers often arrive with dashes, which makes them hard to compare or store in a database. The formula replaces every dash in A2 with an empty string. The empty string is written as two double quotes with nothing between them. The result is a continuous string of digits.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Phone | |||
| 2 | 555-123-4567 | 5551234567 | ||
| 3 | 555-987-6543 | |||
| 4 | 555-222-3333 | |||
| 5 | 555-444-5555 | |||
| 6 | 555-666-7777 | |||
| 7 |
3 Strip a currency symbol
When prices are stored as text with a dollar sign, Excel cannot sum them. The formula removes the dollar sign from A2 by replacing it with nothing. The result is still text, but it can be converted to a number with a function such as VALUE. This is a common first step in cleaning imported financial data.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Price | |||
| 2 | $1,200 | 1200 | ||
| 3 | $850 | |||
| 4 | $2,300 | |||
| 5 | $475 | |||
| 6 | $99 | |||
| 7 |
4 Replace only the second occurrence
Sometimes you need to change one specific match and leave the rest alone. Here the text in A2 contains two hyphens. By setting instance_num to 2, the formula replaces only the second hyphen with a slash. The first hyphen stays as it is. This is useful when a code has a fixed structure and only one separator should change.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | |||
| 2 | AB-12-34 | AB-12/34 | ||
| 3 | CD-56-78 | |||
| 4 | EF-90-12 | |||
| 5 | GH-34-56 | |||
| 6 | IJ-78-90 | |||
| 7 |
5 Update an old department name
A report still uses the old department name 'Sales' but the company now calls it 'Revenue'. The formula replaces 'Sales' with 'Revenue' in A2. Because SUBSTITUTE is case-sensitive, it will not change 'sales' written in lowercase. The result is a report label that matches the current terminology.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Department | |||
| 2 | Sales | Revenue | ||
| 3 | Sales | |||
| 4 | Marketing | |||
| 5 | Sales | |||
| 6 | Finance | |||
| 7 |
Common SUBSTITUTE errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The instance_num argument is not a number, for example a text value or a cell that contains text. | Make sure instance_num is a positive whole number, such as 1, 2, or a reference to a numeric cell. |
| #NAME? | The function name is misspelled, for example SUBSTITUE without the second T. | Check the spelling: SUBSTITUTE. |
| No change in result | old_text is not found because SUBSTITUTE is case-sensitive, or there are extra spaces in old_text. | Match the exact case and spacing, or use a case-insensitive approach such as SUBSTITUTE(UPPER(A2),"OLD","NEW") and adjust as needed. |
| Wrong occurrence replaced | instance_num points to a different occurrence than intended, or is omitted when only one match should change. | Count the occurrences carefully and set instance_num to the exact position you want to replace. |
Tips for using SUBSTITUTE
- Use
""(two double quotes with nothing between) as new_text to delete old_text entirely. - SUBSTITUTE is case-sensitive; if you need case-insensitive replacement, combine it with UPPER or LOWER and then restore the case if necessary.
- To replace more than one character type, nest SUBSTITUTE calls, for example remove both commas and spaces in one formula.
- Remember that SUBSTITUTE returns text; if you remove a currency symbol, wrap the result in VALUE to get a number.
- Use instance_num when a string contains repeated separators and you only want to change one of them.
- Test on a copy of your data first, because SUBSTITUTE does not change the original cell and you may need to paste results as values.
Frequently asked questions
Is SUBSTITUTE case-sensitive?
Yes. SUBSTITUTE treats uppercase and lowercase letters as different. For example, replacing 'sales' will not change 'Sales'.
How do I remove a character completely?
Set new_text to an empty string: =SUBSTITUTE(A2,"-","") removes every dash from A2.
Can I replace only the first occurrence?
Yes. Set instance_num to 1: =SUBSTITUTE(A2,"-","/",1) replaces only the first dash.
What is the difference between SUBSTITUTE and REPLACE?
SUBSTITUTE replaces specific text wherever it appears. REPLACE replaces characters by their position, regardless of what they are.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. SUBSTITUTE reference