=SUBSTITUTE(A2,"-","") removes every dash from the text in A2: SUBSTITUTE finds the old text and puts the new text in its place, here empty text. =SUBSTITUTE(A2,"-",".") would put a dot in place of each dash instead.
| A | B | C | |
|---|---|---|---|
| 1 | Phone | Digits only | With dots |
| 2 | 555-201-3344 | 5552013344 | 555.201.3344 |
| 3 | 555-870-1122 | 5558701122 | 555.870.1122 |
| 4 | 555-433-9001 | 5554339001 | 555.433.9001 |
The result is text, even when only digits are left. Add -- in front, =--SUBSTITUTE(A2,"-",""), when you need a number.
SUBSTITUTE vs REPLACE
=SUBSTITUTE(text, old_text, new_text, [instance_num])
=REPLACE(old_text, start_num, num_chars, new_text)
SUBSTITUTE looks for content: it does not care where old_text is. REPLACE does not look for anything: it counts characters and overwrites num_chars of them from start_num on.
| A | B | |
|---|---|---|
| 1 | Text | Result |
| 2 | Report 2025 final | Report 2026 final |
| 3 | Report 2025 final | Report 2026 final |
| 4 | ID-0042 | SKU-0042 |
| 5 | 5551234 | 555-1234 |
- B2 and B3 give the same result, but B3 only works because the year starts at character 8. If the text changes, SUBSTITUTE still finds it; REPLACE does not.
- B4 swaps the first 2 characters, whatever they are, for
SKU. - B5 uses a length of 0: nothing is removed, so REPLACE inserts the dash after the third character. That is the standard way to insert a character at a position.
Use SUBSTITUTE when you know the text, REPLACE when you know the position. For position-based edits, FIND can supply the position: =REPLACE(A2,FIND(" ",A2),1,"_") replaces the first space.
Replace only the second (nth) occurrence
The fourth argument of SUBSTITUTE picks which occurrence to change. Without it, all of them change:
| A | B | |
|---|---|---|
| 1 | Path | Result |
| 2 | 2026-03-15-final | 2026/03/15/final |
| 3 | 2026-03/15-final | |
| 4 | 2026-03-15 final |
B2 changes every dash. B3, with 2, changes only the second one and gives 2026-03/15-final; B4 puts a space in place of the third. An instance number larger than the count of matches changes nothing.
| A | B | |
|---|---|---|
| 1 | Plan | New name |
| 2 | Basic monthly |
Your turn: In B2, replace Basic with Standard in the plan name in A2, using a formula.
Remove several different characters
SUBSTITUTE handles one old_text per call. For several, nest the calls: each one works on the result of the one inside it.
| A | B | |
|---|---|---|
| 1 | Phone | Digits |
| 2 | (555) 201-3344 | 5552013344 |
| 3 | (555) 870-1122 | 5558701122 |
Read it from the inside out: remove (, then ), then -, then spaces. In Microsoft 365, =REGEXREPLACE(A2,"[^0-9]","") removes every character that is not a digit in one call. For extra spaces specifically, TRIM is the better tool, because it keeps one space between words.
Count occurrences of a character or word
SUBSTITUTE with empty text removes every occurrence, so the length lost is the number of characters removed. Divide by the length of the word to count words:
| A | B | C | |
|---|---|---|---|
| 1 | Text | Commas | "red" appears |
| 2 | red, green, red, blue | 3 | 2 |
| 3 | Red, red, red | 2 | 2 |
C3 returns 2, not 3: SUBSTITUTE is case-sensitive, so Red is not removed. Use SUBSTITUTE(LOWER(A3),"red","") to count regardless of case.
| A | B | |
|---|---|---|
| 1 | Tags | Count |
| 2 | excel,text,formulas,tips |
Your turn: The tags in A2 are separated by commas. In B2, count how many tags there are (one more than the number of commas).
Common mistake: SUBSTITUTE to change a number
SUBSTITUTE works on the text of a number, and the result is text. =SUBSTITUTE(A2,"0","") on 1050 gives 15 as text, and a format such as $1,050.00 is ignored because SUBSTITUTE reads the stored value, 1050. To change numbers, use arithmetic. To change text inside many cells at once, without formulas, Home > Find & Select > Replace (Ctrl+H; Control+H on a Mac) has a Match case option and replaces in place.
Frequently Asked Questions
What is the difference between SUBSTITUTE and REPLACE in Excel?
SUBSTITUTE finds text by its content: =SUBSTITUTE(A2,"old","new") changes every old. REPLACE works by position: =REPLACE(A2,5,3,"new") overwrites 3 characters starting at the 5th, whatever they are.
How do I remove a character from a cell in Excel?
Substitute it with empty text: =SUBSTITUTE(A2,"-","") removes every dash. For several characters, nest the calls: =SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","").
How do I count how many times a character appears in a cell?
Compare the length before and after removing it: =LEN(A2)-LEN(SUBSTITUTE(A2,",","")) counts the commas in A2. For a word, divide by its length: =(LEN(A2)-LEN(SUBSTITUTE(A2,"red","")))/3.
Is SUBSTITUTE case-sensitive?
Yes. =SUBSTITUTE("Red red","red","blue") gives Red blue. To ignore case, lower the text first, =SUBSTITUTE(LOWER(A2),"red","blue"), or use Find and Replace (Ctrl+H), which ignores case by default.
