Excel functions / Text
SUBSTITUTE
Replaces one piece of text with another everywhere it appears.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])Arguments
text | The text to change. |
|---|---|
old_text | What to replace. |
new_text | What to put instead. Use "" to delete it. |
instance_num | Optional. Replace only this occurrence. |
How it works
SUBSTITUTE swaps text by content, not position. It is ideal for cleaning data: removing dashes from codes, swapping full stops for commas, or deleting a unit from "25 kg".
Replacing something with empty quotes deletes it: =SUBSTITUTE(A2,"-","").
Example
| A | B | C | |
|---|---|---|---|
| 1 | Phone | ||
| 2 | 555-0142-77 | 555014277 | |
| 3 |
Result: 555014277. Both dashes are removed.
Tips
- SUBSTITUTE is case-sensitive. Wrap it in VALUE when the cleaned result should be a number.
Practise SUBSTITUTE
Try it yourself in a real spreadsheet, with instant feedback on every answer.