SUBSTITUTE in Excel: how to use it
SUBSTITUTE replaces a particular piece of text with another, everywhere it appears.
Try the first exercises for freeIn the lesson SUBSTITUTE and REPLACE
What does SUBSTITUTE do?
SUBSTITUTE looks for a piece of text in a cell and replaces it with another text, everywhere it appears. With a fourth argument, you only replace one particular occurrence, such as the second.
REPLACE works differently: it doesn't replace by content, but by position. You say which character it starts at, how many characters have to go and what goes in their place. That's handy for codes with a fixed structure.
When do you use SUBSTITUTE?
- Replacing a comma with a point in an amount from a European export: =SUBSTITUTE(A2,",",".").
- Removing a word, such as EUR after an amount: =SUBSTITUTE(A2," EUR","").
- Removing all the spaces from a phone number: =SUBSTITUTE(A2," ","").
- Replacing the first two characters of a code: =REPLACE(A2,1,2,"IE").
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Replaces one piece of text with another, everywhere it appears.
Filled in: =SUBSTITUTE(A1,"-"," ")
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
SUBSTITUTE | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | SUBSTITUTE |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
text | The cell with the text you're changing. | A1 |
old_text | With SUBSTITUTE: the text you want to replace, such as "-". | "-" |
new_text | What goes in its place. With "" you remove something. | " " |
[instance_num] | Optional: only that occurrence of the old text, such as 2 for the second. Leave it out, and Excel replaces them all. | left out |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. | |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. |
SUBSTITUTE step by step
C1 contains =SUBSTITUTE(A1,"-"," "). Replaces each hyphen with a space. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click C1 and type SUBSTITUTE with a bracket, followed by the cell with the text, A1, and a comma.
=SUBSTITUTE(A1, - Type what you want to replace in quotation marks, the hyphen, and a comma.
=SUBSTITUTE(A1,"-", - Type what goes in its place in quotation marks: a space.
=SUBSTITUTE(A1,"-"," " - Close the bracket and press Enter: every hyphen has become a space.
=SUBSTITUTE(A1,"-"," ")
More examples
Removing something
C1 contains =VALUE(SUBSTITUTE(A2," EUR","")). SUBSTITUTE removes the space and EUR, and VALUE turns the text 250 into a number.
Only the second time
C1 contains =SUBSTITUTE(A2,"-","/",2). With 2 as the fourth argument, Excel only replaces the second hyphen.
At a fixed position with REPLACE
C1 contains =REPLACE(A2,1,2,"FR"). REPLACE removes 2 characters from the first character onwards and puts FR in their place, whatever was there.
Where it often goes wrong
- The function is case-sensitive.
- REPLACE works differently: it goes by position, not by text.
Practise this in the lesson SUBSTITUTE and REPLACE. The first exercises are free.
Frequently asked questions
How do I only replace the second occurrence?
Give 2 as the fourth argument.
Read next
LEFT, RIGHT and MIDTRIMLENUPPER, LOWER and PROPERSEARCH and FIND