How to Use SUBSTITUTE in Google Sheets
=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number]). Without the last argument, every match is replaced.
What SUBSTITUTE does
SUBSTITUTE swaps one piece of text for another inside a cell. Use it to remove hyphens from phone numbers, fix a repeated typo or change a separator, without editing each cell by hand.
SUBSTITUTE syntax
=SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
text_to_search: the text to look in, usually a cell.search_for: the text to find. It also matches parts of words, so searching for"vent"changes part of "eventual".replace_with: the text that takes its place.occurrence_number(optional): which match to replace. If you leave it out, all matches are replaced.
A step-by-step example
Cell A2 holds a phone number with hyphens.
| A | |
|---|---|
| 1 | Phone |
| 2 | 090-1234-5678 |
To remove all the hyphens:
=SUBSTITUTE(A2, "-", "")
This returns 09012345678. Here is why:
- Sheets finds every
-in the text. - It replaces each one with an empty string, which removes it.
Replace only one occurrence
Add the fourth argument to choose which match changes. To replace just the first hyphen with a slash:
=SUBSTITUTE(A2, "-", "/", 1)
This returns 090/1234-5678. Google's help page notes that you can replace one instance or all of them, but not a chosen subset of several in a single call.
Common SUBSTITUTE mistakes
- The result is text. Even if it looks like a number, SUBSTITUTE returns text. Wrap it in VALUE to get a number.
- Matching inside other words. Short search strings can change more than you intend.
- Arguments in the wrong order. The text to search comes first, the new text last.
- Assuming case rules. Google's page does not say whether matching ignores case, so test your own data before relying on it.
Try it yourself
Use the phone number above (A2 holds 090-1234-5678). Type your formula into the box, starting with =, then press Check.
Exercise 1: Remove the hyphens
Write a formula that removes every hyphen from the text in A2.
Exercise 2: Only the first one
Write a formula that replaces only the first hyphen in A2 with a slash (/).
What to learn next
- TRIM: remove extra spaces from text.
- LEFT, RIGHT and MID: pull part of a text string by position.
- SPLIT: break text into separate cells.
- FIND: locate text inside a string.
- REGEXEXTRACT: pull text out with a pattern.
- REPLACE: swaps text by position instead of by content.
Syntax and rules in this guide were checked against Google's SUBSTITUTE help page. See all guides.