REPLACE: By Position, Not by Content

About this lesson

REPLACE swaps a run of characters, chosen by starting position and length, for new text. It does not care what those characters are. A length of zero turns it into an insert.

By the end you can

replace characters by position with REPLACE.
insert text into a cell with a count of zero.
choose between REPLACE and SUBSTITUTE from the data.

Practises REPLACE SUBSTITUTE MID

The idea

SUBSTITUTE, from the building module, finds text and changes it. REPLACE counts to a position and changes whatever is there. That makes REPLACE the tool for fixed layouts. A year that always sits in characters 5 to 8, or a card number whose first twelve digits are masked. It is the wrong tool for anything whose shape varies.

The trick to remember is a count of zero. REPLACE(G2,3,0,"-") removes nothing and inserts a hyphen at position 3. That is the only way to insert into text without joining two pieces with an ampersand.

The mistake to watch for

Using REPLACE on text whose shape varies. It counts to a position and changes whatever is there. So a code that is one character longer than the others has the wrong part replaced, without any error. REPLACE is for fixed layouts. When you know what the text says, not where it sits, use SUBSTITUTE.

Where this comes up again

LEFT and RIGHT: Taking from the Ends Text: Extracting
MID: Taking from the Middle Text: Extracting
FIND: Locating a Character Text: Extracting
LEFT with FIND: Splitting a Name 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

In B2, mask the phone number: replace its first seven characters with seven asterisks, using REPLACE.

To begin, type it exactly:

=REPLACE(A2,1,7,"*******")

Row	A	B	D	E	G	H
1	Phone	Masked	Invoice	Renumbered	Code	With dash
2	07700900123		INV-2023-041		AB1042
3	07700900456		INV-2023-042		CD77
4
5
6
7
8

Every step

1

In B2, mask the phone number: replace its first seven characters with seven asterisks, using REPLACE. To begin, type it exactly: =REPLACE(A2,1,7,"*******").

Hint. Start at 1, replace 7.
2

The invoice numbers carry last year. In E2, replace the four characters of the year, which start at position 5, with 2024.

Hint. Position 5, count 4.
3

Read =REPLACE(D3,5,4,"2024") and say what it returns.

Hint. Swap characters 5 to 8.
4

In E3, do the same job with SUBSTITUTE instead: swap the text 2023 for 2024 wherever it appears.

Hint. Find 2023, put 2024.
5

In H2, write a formula using REPLACE that inserts a hyphen after the two letters of the code in G2. So AB1042 becomes AB-1042. Replace zero characters at position 3.

Hint. Count 0.