Formula – Remove Unwanted Characters – Excel
In this Article
This tutorial will demonstrate how to remove unwanted character(s) from a cell in Excel.
Remove Unwanted Characters
The SUBSTITUTE Function replaces characters in a text string. Its syntax is:
If you want to remove unwanted characters from a cell, just use the SUBSTITUTE Function with the character to be removed and an empty string (“”).
1 |
=SUBSTITUTE(B3," ","") |
The example above removes spaces from the original string by replacing them with an empty string. It works for any character, like the exclamation point example below:
1 |
=SUBSTITUTE(B3,"!","") |
Nested SUBSTITUTE Functions
With the SUBSTITUTE Function, as shown in the examples above, it’s only possible to remove one character (for any number of instances) at a time. But you can remove multiple unwanted characters at once by nesting SUBSTITUTE Functions.
1 |
=SUBSTITUTE(SUBSTITUTE(B3,"#","-"),"--","-") |
This example removes any the pound signs “#” or double dashes “–” and replaces them with a single dash “-“.
Remove Unwanted Characters in Google Sheets
These formulas work exactly the same in Google Sheets as in Excel.