Text function

SUBSTITUTE

Swap this text for that text.

Purpose

Swap one piece of text for another

Returns

The value with the matched text replaced

Syntax

=SUBSTITUTE(text, old_text, new_text, [which_one])

Argument	Required	What goes here
`text`	Required	The cell holding the text to change, usually A2.
`old_text`	Required	The text to swap out, in double quotes.
`new_text`	Required	The text to put in its place, in double quotes. Use "" to take it out.
`which_one`	Optional	Which match to change, when you only want one of them.

SUBSTITUTE looks for text you name and puts something else in its place, everywhere it appears.

Give it an empty pair of quotation marks as the replacement and it deletes instead, which is how a stray prefix or a thousands separator comes out.

The optional fourth argument picks one occurrence rather than all of them: 2 changes only the second match and leaves the rest.

Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.

Supplier import — raw.xlsxSheet1

Formula	Result
`=SUBSTITUTE(A2,"-","/")`Every dash becomes a slash, because no occurrence was named.	GL/6100/8801
`=SUBSTITUTE(A2,"GL-","")`Replacing with nothing is how you delete. The prefix comes off.	6100-8801
`=SUBSTITUTE(A2,"-","/",2)`Only the second dash changes. The first is left alone.	GL-6100/8801
`=LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))`Count how many dashes there are by measuring how much shorter the value gets without them.	2

SUBSTITUTE matches capitals exactly. "ltd" and "Ltd" are two different things to it.
It works on text it can find. REPLACE works on positions instead, which is what you want when there is nothing distinctive to match.