Download the example workbook
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.
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.