Formula – Remove Unwanted Characters – Excel
Download the example workbook
This tutorial will demonstrate how to remove unwanted character(s) from a cell in Excel.
Remove Unwanted Characters
If you want to remove unwanted characters from a cell, use the SUBSTITUTE Function to replace the unwanted character with blank text (“”). Here we will remove all spaces from a cell:
1 |
=SUBSTITUTE(B3," ","") |
This example will replace an exclamation mark (!):
1 |
=SUBSTITUTE(B3,"!","") |
Nested SUBSTITUTE Functions
With the SUBSTITUTE Function, as shown in the examples above, it’s only possible to remove one text string (or character) 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.