SUBSTITUTE: Replacing Text

About this lesson

SUBSTITUTE replaces one piece of text inside another. It changes every occurrence unless you tell it which one you mean.

By the end you can

replace one piece of text with another using SUBSTITUTE.
replace only the second occurrence, or remove a character entirely.

Practises SUBSTITUTE TRIM FIND

By Ali, Founder, Excel Lessons

The idea

Three arguments to start: the text, what to look for, what to put there instead. Replacing something with an empty pair of quotation marks removes it. That is how most people use it: stripping spaces out of a phone number, or hyphens out of a code.

Two more things to know. The fourth argument says which occurrence to change. That is why SUBSTITUTE turns up in formulas that split text. Replace the second separator with a character that appears nowhere else, then FIND that instead of counting. And SUBSTITUTE is case sensitive, and it will not tell you. Ask it to replace inv in a reference that says INV and it returns the text unchanged, with no error. A substitution that seems to have done nothing is nearly always this.

The mistake to watch for

Asking for the wrong case. SUBSTITUTE matches capitals exactly and fails without a sound. Ask it to replace inv in a reference that says INV. It returns the text unchanged, with no error and no warning. A substitution that seems to have done nothing is nearly always this. LOWER or UPPER on the way in is the fix.

Where this comes up again

Splitting Text: Putting It Together Text: Splitting
Why VLOOKUP Returns #N/A Errors and Robust Formulas
LEN: Counting Characters Text: Extracting
MID: Taking from the Middle Text: Extracting

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

SUBSTITUTE replaces one piece of text with another.

In `B2`, type:

=SUBSTITUTE(A2,"-","/")

Remember: old and new text both go in quotes.

Row	A	B
1	Reference	Rewritten
2	INV-2024-0031
3	INV-2024-0032
4	PO/2024/0088
5
6	Phone	0161 496 0208
7
8

Every step

1

SUBSTITUTE replaces one piece of text with another. In B2, type: =SUBSTITUTE(A2,"-","/"). Remember: old and new text both go in quotes.

Hint. Three arguments in that order.
2

There is a fourth argument that says which one. Read =SUBSTITUTE(A2,"-","/",2) and say what it returns.

Hint. Which hyphen is the second one?
3

Replacing with nothing removes it. In C6, remove every space from B6.

Hint. What do you replace a space with to make it disappear?
4

In B4, replace every slash in A4 with a hyphen, using SUBSTITUTE.

Hint. Old first, new second.
5

SUBSTITUTE cares about case. Read =SUBSTITUTE(A2,"inv","REF") and say what it returns.

Hint. Does the case match?