Gridwise
Sign inStart free
Text function

How to use SUBSTITUTE in Excel

Replaces text inside text.

Syntax

SUBSTITUTE(text, old_text, new_text, [instance_num])
textText or a cell
old_textWhat to find
new_textWhat to put instead
[instance_num]Which occurrence (optional) optional

Example

Here’s SUBSTITUTE on a small table of orders. The highlighted cells are the ones the formula reads.

G2
fx
=SUBSTITUTE("555-0142","-","")
A
B
C
D
E
F
1
Order
Region
Rep
Units
Price
Revenue
2
1001
East
Maya
4
12
48
3
1002
West
Omar
2
30
60
4
1003
East
Lena
7
12
84
5
1004
North
Maya
1
45
45
6
1005
West
Lena
5
30
150
7
1006
East
Omar
3
18
54
8
1007
North
Lena
6
12
72
9
1008
West
Maya
8
18
144
5550142Result of =SUBSTITUTE("555-0142","-","")

Edit this formula live Change the ranges and watch the answer update. Free, no sign-up.

Common mistakes with SUBSTITUTE

It’s case-sensitive

SUBSTITUTE("Apple","a","") leaves the capital A. Use UPPER/LOWER first if case varies.

Replace with "" to delete

SUBSTITUTE(A2,"-","") removes every dash.

What the error messages mean

Type these slips into Gridwise and this is the feedback you get:

Leaving off the closing bracket

=SUBSTITUTE("555-0142","-",""

Parentheses don’t match. You have 1 opening and 0 closing parentheses. Every ( needs a ).

Questions

What does the SUBSTITUTE function do in Excel?

Replaces text inside text. The syntax is SUBSTITUTE(text, old_text, new_text, [instance_num]).

Why is my SUBSTITUTE formula giving the wrong answer?

It’s case-sensitive: SUBSTITUTE("Apple","a","") leaves the capital A. Use UPPER/LOWER first if case varies. Replace with "" to delete: SUBSTITUTE(A2,"-","") removes every dash.

Does SUBSTITUTE work in Google Sheets?

Most Excel functions, including the core ones on this page, work the same way in Google Sheets. A few newer functions arrived in Sheets later or behave slightly differently, so test it on your own data.

Related functions