# SUBSTITUTE function in Excel

> SUBSTITUTE replaces specific text with new text everywhere it appears, or only at the occurrence you choose.

Source: https://spreadsheetpractice.com/functions/substitute · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on November 20, 2026

## Quick answer

- **What it does:** SUBSTITUTE replaces specific text with new text everywhere it appears, or only at the occurrence you choose.
- **Syntax:** `=SUBSTITUTE(text, old_text, new_text, [instance_num])`
- **Most common use:** `=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","")," ",""),"-","")` returns 6125550148 (Nested to strip four kinds of characters)
- **Available in:** Every current version
- **Category:** [Text functions](https://spreadsheetpractice.com/functions/category/text)
- **Returns:** Text with the replacements made.
- **Google Sheets:** Works the same in Google Sheets.

## What it does

SUBSTITUTE replaces specific text with new text everywhere it appears, or only at the occurrence you choose. Use it to strip dashes from phone numbers, swap an old product name for a new one, or remove characters that block a lookup.

## Syntax

`=SUBSTITUTE(text, old_text, new_text, [instance_num])`

| Argument | Required? | What it is |
|---|---|---|
| `text` | Required | The text or cell to change. |
| `old_text` | Required | The text to find. The match is case-sensitive. |
| `new_text` | Required | What to put in its place. Use "" to delete it. |
| `instance_num` | Optional | Which occurrence to replace. Leave it out to replace all of them. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | `=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","")," ",""),"-","")` | 6125550148 | Nested to strip four kinds of characters |
| D3 | `=SUBSTITUTE(B3,"2025","2026")` | Gadget 2026 edition | An old model year swapped for the new one |
| D4 | `=SUBSTITUTE(C4,"/"," > ",2)` | hr/2025 > q4/review | Only the second slash is replaced |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D |
|---|---|---|---|---|
| 1 | Phone | Product | Path | Result |
| 2 | (612) 555-0148 | Widget Pro 2025 | sales/2026/q1/report | 6125550148 |
| 3 | 763-555-0193 | Gadget 2025 edition | ops/2026/q2/plan | Gadget 2026 edition |
| 4 | 952.555.0107 | Gizmo Mini | hr/2025/q4/review | hr/2025 > q4/review |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| Nothing gets replaced | SUBSTITUTE is case-sensitive, so "widget" won't match "Widget". Match the case exactly, or wrap the text in LOWER first. |
| The result won't add up | SUBSTITUTE returns text even when it looks like a number. Convert it: =VALUE(SUBSTITUTE(A2,"$","")). |
| You know the position, not the text | If the characters change but their position doesn't, use REPLACE instead. |

## Tips

- Count how often something appears: =LEN(A2)-LEN(SUBSTITUTE(A2,"-","")) counts the dashes.

## Related functions

- [REPLACE](https://spreadsheetpractice.com/functions/replace): REPLACE swaps out a set number of characters at a given position, whatever those characters are.
- [TRIM](https://spreadsheetpractice.com/functions/trim): TRIM removes spaces from the start and end of text and shrinks runs of spaces between words to one.
- [CLEAN](https://spreadsheetpractice.com/functions/clean): CLEAN removes non-printing characters, such as line breaks and tabs, from text.

## Questions

### What is the difference between SUBSTITUTE and REPLACE?

SUBSTITUTE finds text by content, such as "replace every dash". REPLACE works by position, such as "replace 3 characters starting at position 1".

### How do I remove a character from a cell?

Substitute it with nothing: =SUBSTITUTE(A2,"-",""). Nest SUBSTITUTE to remove several different characters.

## Practice

A graded SUBSTITUTE exercise ("Digits only") is on [the SUBSTITUTE page](https://spreadsheetpractice.com/functions/substitute). It is checked instantly, then re-checked on changed data.

Lessons that use SUBSTITUTE:

- [Lesson 20: Cleaning messy data](https://spreadsheetpractice.com/learn/cleaning-messy-data) (Pro)
- [Lesson 22: Splitting and counting list cells](https://spreadsheetpractice.com/learn/textsplit-count-clean-lists) (Pro)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
