Formula – Remove Unwanted Characters – Excel
Written by
Reviewed by
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:
=SUBSTITUTE(B3," ","")
This example will replace an exclamation mark (!):
=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.
=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.
