Remove Special Characters in Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on February 8, 2023
Download Example Workbook

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 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:

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), "")
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.

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

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

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to Excel Formulas List