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 Main Function

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.

remove special characters 1

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:

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.



Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!