Text
SUBSTITUTE
Definition
SUBSTITUTE replaces one set of characters with another inside a text string.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])
Arguments
| Argument | Meaning |
|---|---|
text |
The text, or the cell holding it, to change. |
old_text |
The characters to find. Case must match. |
new_text |
The characters to put in their place. |
instance_num |
Optional. Which occurrence to replace. If left out, Excel replaces every match. |
Worked example
A paint list uses the spelling grey and needs gray. The labels are in A2:A4.
| A: Label |
|---|
| grey door |
| blue door |
| grey gate |
Formula: =SUBSTITUTE(A2,"grey","gray")
Result: gray door. The letters grey are replaced with gray, and the rest of the label stays the same.
Practice
Cell A2 holds cat bat. In B2, write a formula that replaces at with ake in that text.
- In B2, write a SUBSTITUTE formula that replaces at with ake in A2.
- B2 should show A2 after replacing at with ake.
Step 1 of 2
Enter =SUBSTITUTE(A2,"at","ake") in B2.
Replacing at with ake in cat bat gives cake bake.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
B2 uses SUBSTITUTE.
B2 now shows cake bake.
Related functions
TRIM can tidy spaces in the same string before or after a replace, and LEFT can then take a prefix from the new text.
Common mistake
Expecting SUBSTITUTE to ignore case: Grey is not replaced by a search for grey, because the letters do not match exactly.