Excel SUBSTITUTE Function
SUBSTITUTE Function
The SUBSTITUTE function is a premade function in Excel, which replaces text in a cell with new text.
It can replace every occurrence of the text, or only one of them.
It is typed =SUBSTITUTE and has the following parts:
=SUBSTITUTE(text, old_text, new_text, [instance_num])
Note: The different parts of the function are separated by a symbol, like comma , or semicolon ;
The symbol depends on your Language Settings.
Text: The text to change. This is usually a cell.
Old_text: The text to replace. Type it inside double quotes, like "MEDS".
New_text: The text to put in its place, also inside double quotes.
Instance_num: Optional. Which occurrence of old_text to replace, like 2 for the second one. If you leave it out, every occurrence is replaced.
Note: SUBSTITUTE is case-sensitive. "ball" does not match Ball. You can read more about this further down the page.
How to use the SUBSTITUTE function:
- Select a cell (
C2) - Type
=SUBSTITUTE - Double click the SUBSTITUTE command
- Select the cell with the text (
B2) - Type (
,) - Type the text to replace, inside double quotes (
"MEDS") - Type (
,) - Type the new text, inside double quotes (
"HEAL") - Hit enter
Let's have a look at an example!
These are the products in the Poke Mart shop, with their item codes. Copy them and paste them into cell A1 to follow along:
Example data
A B 1 Product Item Code 2 Poke Ball PKB-0200-BALL 3 Great Ball GRB-0600-BALL 4 Ultra Ball ULB-1200-BALL 5 Potion POT-0300-MEDS 6 Super Potion SPT-0700-MEDS 7 Hyper Potion HPT-1200-MEDS 8 Revive REV-1500-MEDS 9 Antidote ANT-0100-STAT 10 Paralyze Heal PRH-0200-STAT 11 Awakening AWK-0250-STAT
The last part of each item code is the category: BALL, MEDS or STAT. The shop has decided to rename the MEDS category to HEAL.
Type the label New Code in C1.
Use the SUBSTITUTE function to make the new codes:
| A | B | C | |
|---|---|---|---|
| 1 | Product | Item Code | New Code |
| 2 | Poke Ball | PKB-0200-BALL | PKB-0200-BALL |
| 3 | Great Ball | GRB-0600-BALL | GRB-0600-BALL |
| 4 | Ultra Ball | ULB-1200-BALL | ULB-1200-BALL |
| 5 | Potion | POT-0300-MEDS | POT-0300-HEAL |
| 6 | Super Potion | SPT-0700-MEDS | SPT-0700-HEAL |
| 7 | Hyper Potion | HPT-1200-MEDS | HPT-1200-HEAL |
| 8 | Revive | REV-1500-MEDS | REV-1500-HEAL |
| 9 | Antidote | ANT-0100-STAT | ANT-0100-STAT |
| 10 | Paralyze Heal | PRH-0200-STAT | PRH-0200-STAT |
| 11 | Awakening | AWK-0250-STAT | AWK-0250-STAT |
=SUBSTITUTE(B2, "MEDS", "HEAL")
The function is repeated with the filling function for each row, down to C11.
The four codes with MEDS now have HEAL instead, like POT-0300-HEAL. The other codes do not contain MEDS, so SUBSTITUTE returns them unchanged.
Replace One Occurrence or All
Each item code has two dashes. Without instance_num, SUBSTITUTE replaces both of them. With instance_num, it replaces only the one you choose.
Type PKB-0200-BALL in A2, A3 and A4 of a new sheet, and replace the dashes with spaces:
| A | B | |
|---|---|---|
| 1 | Code | Result |
| 2 | PKB-0200-BALL | PKB 0200 BALL |
| 3 | PKB-0200-BALL | PKB 0200-BALL |
| 4 | PKB-0200-BALL | PKB-0200 BALL |
The formulas in B2, B3 and B4:
=SUBSTITUTE(A2, "-", " ")
=SUBSTITUTE(A3, "-", " ", 1)
=SUBSTITUTE(A4, "-", " ", 2)
B2 has no instance_num, so both dashes are replaced.
B3 has instance_num 1, so only the first dash is replaced.
B4 has instance_num 2, so only the second dash is replaced.
Remove Characters
To remove a character, replace it with an empty text. An empty text is two double quotes with nothing between them: ""
Go back to the Poke Mart sheet. Type the label No Dashes in D1, and remove the dashes from the item codes. Fill the formula down to D11:
| B | D | |
|---|---|---|
| 1 | Item Code | No Dashes |
| 2 | PKB-0200-BALL | PKB0200BALL |
| 3 | GRB-0600-BALL | GRB0600BALL |
| 4 | ULB-1200-BALL | ULB1200BALL |
| 5 | POT-0300-MEDS | POT0300MEDS |
| 6 | SPT-0700-MEDS | SPT0700MEDS |
| 7 | HPT-1200-MEDS | HPT1200MEDS |
| 8 | REV-1500-MEDS | REV1500MEDS |
| 9 | ANT-0100-STAT | ANT0100STAT |
| 10 | PRH-0200-STAT | PRH0200STAT |
| 11 | AWK-0250-STAT | AWK0250STAT |
=SUBSTITUTE(B2, "-", "")
Every dash is replaced with nothing, so PKB-0200-BALL becomes PKB0200BALL.
The same works for spaces. =SUBSTITUTE(A2, " ", "") changes Poke Ball into PokeBall.
SUBSTITUTE is Case-Sensitive
SUBSTITUTE only replaces text that has exactly the same uppercase and lowercase letters as old_text.
Type Poke Ball in A2 and A3 of a new sheet, and try to replace Ball with Orb:
| A | B | |
|---|---|---|
| 1 | Product | Result |
| 2 | Poke Ball | Poke Ball |
| 3 | Poke Ball | Poke Orb |
The formulas in B2 and B3:
=SUBSTITUTE(A2, "ball", "Orb")
=SUBSTITUTE(A3, "Ball", "Orb")
B2 looks for ball with a lowercase b. There is none in Poke Ball, so nothing is replaced, and there is no error either.
B3 looks for Ball with an uppercase B, and finds it.
Note: Excel's Find and Replace tool (Ctrl+H) works differently. By default, it does not care about uppercase and lowercase. It only matches the case if you check Match case in its options.
If a replacement seems to do nothing, check the uppercase and lowercase letters in old_text first. You can use UPPER, LOWER or PROPER to make the letter case the same before you replace.
Related Functions
To remove extra spaces, use TRIM. To count how many times a character appears, combine SUBSTITUTE with LEN.
