SUBSTITUTE
Replaces one piece of text with another: removes dashes from phone numbers, swaps an old name for a new one.
SUBSTITUTE(text, old_text, new_text, [instance_num])- [ ]optional part
- text
- Where to replace, for example cell A2.
- old_text
- What to replace, in quotes.
- new_text
- What to replace it with. Empty quotes "" just delete the piece.
- instance_num
- Which occurrence to replace. If left out, all of them are replaced.Optional
Return value
Text with the old piece replaced by the new one: everywhere, or only in the occurrence with the given number.
Examples
Phone numbers without dashes
fx
A
B
C
1
2
3
4
5
6
7
Phone
Digits only
912-345-6789
903-111-2233
926-555-0417
915-020-8008
Sheet1
Details
SUBSTITUTE finds every occurrence of the old text and replaces it with the new one. To delete the piece, leave the new text empty: "".
The fourth argument lets you replace just one occurrence, for example the second. Without it, all of them are replaced.
The function is case-sensitive: replacing "usd" won't touch "USD".
Common mistakes
- The old and new text are swapped: nothing gets replaced.
- The case doesn't match. SUBSTITUTE looks for the exact spelling, and uppercase and lowercase letters are different for it.
- An occurrence number is given when you need to replace all of them: only one gets replaced.
- After removing characters only digits are left, but it's still text. Wrap the formula in VALUE to calculate with it.