Remove Special Characters in Excel & Google Sheets

This tutorial will demonstrate you how to remove all special characters from cells in Excel and Google Sheets.

Remove Special Characters

Excel does not provide any functions to remove all special characters from strings at once. If you want to remove only one special character, you can use the SUBSTITUTE function (see more in this article Remove Unwanted Characters).

If you do want to use a single formula to remove special characters, you will need to use a User Defined Function (UDF), like RemoveSpecChar.

=RemoveSpecChar(B3)

In order to make your UDF work, open the Visual Basic Editor by pushing Alt+F11. Right-click on your workbook’s name in the Project window on the left-hand side and insert a new module. Into this module copy the below UDF:

Function RemoveSpecChar(sInput As String) As String
Dim sSpecChar As String
Dim i As Long
sSpecChar = "\/:*?™""®<>|.&@# (_+`©~);-+=^$!,'"
For i = 1 To Len(sSpecChar)
sInput = Replace$(sInput, Mid$(sSpecChar, i, 1), "")
Next
RemoveSpecChar = sInput
End Function

Now your UDF is defined and ready to use. Do not forget to save your workbook as an Excel Macro-Enabled Workbook (*.xlsm).

To Learn more about UDFs, please read this article: User Defined Functions.