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

By Ali, Founder, Excel Lessons

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

REPLACE swaps characters by position and length.

In `B2`, type:

=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

REPLACE swaps characters by position and length. In B2, type: =REPLACE(A2,1,7,"*******").

Hint. Start at 1, replace 7.
2

Invoice numbers carry last year. In E2, replace 4 characters starting at position 5 with 2024. Shape: =REPLACE(cell,start,n,"new").

Hint. Start at 5, count 4 characters.
3

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

Hint. Swap characters 5 to 8.
4

SUBSTITUTE finds text anywhere, not by position. In E3, swap 2023 for 2024 with SUBSTITUTE. Shape: =SUBSTITUTE(cell,"old","new").

Hint. Find 2023, put 2024.
5

In H2, insert a hyphen after the 2 letters in G2. Shape: =REPLACE(cell,start,0,"new"). Remember: a length of 0 inserts instead of replacing.

Hint. Count 0.