Excel Practice
Lessons Lesson 65

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

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

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

The system downstream wants slashes instead of hyphens. In B2, rewrite the reference in A2 with every hyphen replaced by a slash.

To begin, type it exactly:

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

B2
Row A B C D E F G
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. 1

    The system downstream wants slashes instead of hyphens. In B2, rewrite the reference in A2 with every hyphen replaced by a slash. To begin, type it exactly: =SUBSTITUTE(A2,"-","/").

    Hint. Three arguments in that order.

  2. 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. 3

    Remove something rather than replace it. In B6, give the phone number in B6 with every space taken out.

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

  4. 4

    In B4, rewrite the purchase order reference in A4 so it uses hyphens instead of slashes. Use SUBSTITUTE.

    Hint. Old first, new second.

  5. 5

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

    Hint. Does the case match?