How to use SUBSTITUTE to remove a character from text
Codes arrive with dashes, spaces or slashes you do not want. SUBSTITUTE swaps one piece of text for another, and swapping it for nothing removes it.
The formula
=SUBSTITUTE(A2,"-","") in column B
What each part does
A2- The text to work on.
"-"- What to look for. Every dash in the text is found, not just the first.
""- What to put in its place. Nothing at all, so the dashes simply go.
How it works, and what to watch for
The general idea: give it the text, what to look for, and what to put in its place. Replacing with two empty quotes removes the character.
Every match is replaced, not just the first. If you only want one of them, a fourth part says which: =SUBSTITUTE(A2,"-","",2) changes the second dash alone.
It is case sensitive, so a lower-case letter will not match an upper-case one. REPLACE is the related function, but it works by position rather than by content.
What comes back is text. A code such as 00123 stays as it is, which is usually what you want, but it means the result will not add up as a number.
Questions
What is the difference between SUBSTITUTE and REPLACE?
SUBSTITUTE looks for a piece of text wherever it appears. REPLACE changes a fixed number of characters at a position you name.
How do I remove spaces instead?
Use =SUBSTITUTE(A2," ",""). To clear only the extra spaces at the ends and between words, TRIM is the better tool.
Can I remove two different characters?
Yes, by nesting: =SUBSTITUTE(SUBSTITUTE(A2,"-",""),"/",""). Each layer removes one character.
The example sheet
| Reference |
Cleaned |
| INV-2026-001 |
|
| INV-2026-002 |
|
| INV-2026-003 |
|
| INV-2026-004 |
|
If this helped, these are close by
More on SUBSTITUTE, and every walkthrough that uses it.
