Remove dashes
| A | |
|---|---|
| 1 | Phone |
| 2 | 555-123-4567 |
Formula: C1: =SUBSTITUTE(A2,"-","")
Result: 5551234567
An empty string as new_text deletes the old text.
Text
SUBSTITUTE
Replaces text by its content.
Syntax
SUBSTITUTE(text, old_text, new_text, [instance_num])Example: =SUBSTITUTE(A2,"-","")
Available in: All versions
SUBSTITUTE replaces specific text inside a string: =SUBSTITUTE(text, old_text, new_text, [instance_num]). It is the tool for removing dashes from phone numbers, swapping characters or replacing only the nth occurrence.
When to use it
- You remove or replace a known character or word across many cells.
- You want to change only the second or third occurrence of a character.
- You count how often a character appears in a cell.
- You prepare data for matching, such as removing hyphens.
Arguments explained
- text. The string in which you want to replace pieces, usually a cell reference.
- old_text. The exact text to find. Matching is case sensitive and wildcards are not supported.
- new_text. The replacement. Use "" to delete old_text.
- instance_num. Optional. Replace only the nth occurrence of old_text. If omitted, every occurrence is replaced.
Worked examples
Replace one occurrence only
| A | |
|---|---|
| 1 | List |
| 2 | a,b,c,d |
Formula: C1: =SUBSTITUTE(A2,",",";",2)
Result: a,b;c,d
The fourth argument 2 limits the change to the second comma.
Count a character
| A | |
|---|---|
| 1 | Tags |
| 2 | red,green,blue |
Formula: C1: =LEN(A2)-LEN(SUBSTITUTE(A2,",",""))
Result: 2
Removing every comma shortens the text by the number of commas.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in D2: =SUBSTITUTE(A5,"-","/")
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Text | Other | Result | |
| 2 | Alice Johnson | alice johnson | SKU/2026/0415 | |
| 3 | alice@shop.com | ALICE@shop.com | ||
| 4 | extra spaces | |||
| 5 | SKU-2026-0415 | |||
| 6 | iced coffee, tea, sugar | |||
| 7 | Order 417 shipped |
Result in D2: SKU/2026/0415
Common errors and how to fix them
- Case mismatch. SUBSTITUTE is case sensitive. "Pen" does not replace "pen".
- Result is text. Cleaned numbers such as "5551234567" are text. Multiply by 1 if you need a number.
- Nothing changes. The old text has to match exactly, including hidden spaces. Check with LEN or FIND.
- Confusion with REPLACE. REPLACE changes characters by position; SUBSTITUTE changes them by content.
- Wildcards do not work. Characters such as * and ? are treated as plain text in old_text. For pattern-based cleanup, use Find and Replace or a combination of FIND, LEFT and RIGHT.
- Replacing several different items. Nest the calls, for example SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""), working from the inside out, so each step cleans the result of the previous one.
Related functions
Frequently asked questions
- What is the difference between SUBSTITUTE and REPLACE?
- SUBSTITUTE finds text and swaps it. REPLACE swaps characters at a given position and length.
- Can SUBSTITUTE replace several different characters?
- Nest SUBSTITUTE calls, one per character.
- Is SUBSTITUTE case sensitive?
- Yes.
- Does SUBSTITUTE support wildcards?
- No. Wildcards are not applied in SUBSTITUTE.