The SUBSTITUTE function
Replacing a piece of text with another: a comma with a point, a non-breaking space with nothing, an old product name with the new one.
Syntax
=SUBSTITUTE(text, old_text, new_text, [instance_num])
In a French Excel: =SUBSTITUE(texte; ancien_texte; nouveau_texte; [no_position]).
Arguments
- text: the cell.
- old_text: what to replace; case matters.
- new_text: with what;
""to delete. - instance_num: only the n-th occurrence.
An example
=VALUE(SUBSTITUTE(SUBSTITUTE(A2,",",""),"$",""))
Removes the thousands separators and the dollar sign from "$1,234", then converts to a number.
Pitfalls
- Non-breaking spaces are not spaces:
CHAR(160)refers to them. - Case matters: "North" is not "NORTH".
Going further
- TRIM.
- Convert text to numbers and Clean text: across a whole file, with no formula.
Frequently asked questions
- What is the difference with REPLACE?
- SUBSTITUTE replaces one text with another; REPLACE replaces characters at a given position, whatever they are.