Excel track · Exercise 65 of 86 Paid

SUBSTITUTE: replace one piece of text

Replace a separator everywhere, then name a single occurrence with the fourth argument.

Intermediate

Booking references that carry two hyphens in fixed places

Read-only data · screen 1 of 2 · answer in B2

In B2, replace every hyphen in the booking reference in A2 with a slash.

	A	B
1	Reference	Answer
2	WH-12-03
3	AB-CD-EF
4	ZZ-99-01

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

In B2, replace only the second hyphen in the booking reference in A2 with a slash.

	A	B
1	Reference	Answer
2	WH-12-03
3	AB-CD-EF
4	ZZ-99-01

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

Reference answer

What it does

SUBSTITUTE takes the text, the piece to look for and the piece to put in its place. It swaps every match in the string, so the call =SUBSTITUTE(A2,"-","/") turns WH-12-03 into WH/12/03. Nothing outside the match changes.

This column holds three booking references, each with two hyphens. The first screen replaces both, which is what the three-argument form does. The second adds 2 as a fourth argument, and only the second hyphen becomes a slash: WH-12/03.

The number counts occurrences of the piece you are replacing, from the left, so 2 points at the second hyphen in the string. A replace at a fixed position names a place, and breaks as soon as a reference changes length.

Syntax

=SUBSTITUTE(text,old,new,[instance]) swaps old for new everywhere, or at one instance

Worked example

On Booking references that carry two hyphens in fixed places, =SUBSTITUTE(A2,"-","/") returns WH/12/03.

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

Common errors

Error codes for SUBSTITUTE replacing all matches, then one named occurrence
Error	What it means and how to fix it
`#VALUE!`	The fourth argument is below 1 or is not a number, which names no occurrence to replace.
`#NAME?`	SUBSTITUTE is misspelled, and Excel returns #NAME?.
`Nothing replaced`	An occurrence number past the end of the text returns the reference unchanged, and the grader sees an untouched answer.

At work

Imported codes that need a different separator before another system will read them.