How To Remove Special Characters in Excel

,

This post will show you how to remove special characters from text strings in Excel. And I am going to introduce two ways to remove special characters (@&)#%$) from a string in Excel.

1. Removing Special Characters using Power Query

If you are using Excel 2010 or Excel 2013, and you need to install power query as add-in in your workbook, and you can download it from the Microsoft website and then install it as add-in. And if you are using Excel 2016 or excel 365, and this plugin is pre-installed.

Assuming that you have power query installed, and you can click into your data somewhere, and go up to DATA on your ribbon, and click on From Table/Range button under Get & Transform Data group.

remove special character1

The Create Table dialog box will appear. And just confirming the range of cells you are working on. And you also need to be mindful of that little check box “my table is headers”if you already have a heading, just click ok button.

remove special character2

Then the Power Query dialog box will appear. and that will show you the data that you had in your worksheet.

remove special character2

The below steps will show how to remove special characters in the above power query tool:

Step1: go to Add Column Tab in the Power Query Editor window, and click on Custom Column button under General group. And the Custom Column dialog box will appear.

remove special character2

Step2: enter one new column name as “without special characters”, then type the below formula in the Custom column formula box.

=Text.Select([FIRST NAME],{"A".."z","0".."9"})
remove special character2

Note: you need to instead “FIRST NAME” of your available columns.

Step3: you would see that all the characters are extracted without special characters in the new column.

remove special character2

Step4: you need to go to File Tab, and click on close & Load menu to get it back into your worksheet.

remove special character2

Step5: you would see that you have the cleaned text string in our current worksheet.

remove special character2

2. Removing Special Characters using User Defined Function

You can also create a user defined function to remove the special characters that you want, just do the following steps:

Step 1: On current visible worksheet, right click on sheet name tab to load Sheet management menu. Select View CodeMicrosoft Visual Basic for Applications window pops up.

hide every other row1

Or you can enter Microsoft Visual Basic for Applications window via Developer->Visual Basic. You can also press Alt + F11 keys simultaneously to open it.

How to Remove All Extra Spaces and Keep Only One Between Words 5

Step 2: In Microsoft Visual Basic for Applications window, enter below code:

Function RemoveSpecialCharacters(myString As String) As String

Dim mySpeChars As String

Dim tmp As Long

mySpeChars = "#$%()^*&"

For tmp = 1 To Len(mySpeChars)

myString = Replace$(myString, Mid$(mySpeChars, tmp, 1), "")

Next

RemoveSpecialCharacters = myString

End Function
remove special character2

Step 3: Save code, quit Microsoft Visual Basic for Applications.

Step 4: go back to the worksheet, and you can type the below formula based on your user defined function in Cell C2:

=RemoveSpecialCharacters(A2)
remove special character2

Step 5: then you can drag the Fill Handle in Cell C2 down to the cells which you want to apply this formula to remove all the special characters.

remove special character2

3. Video: Remove Special Characters

This Excel video tutorial will guide you how to remove special characters from text strings in Excel. We’ll explore two powerful methods: utilizing the capabilities of Power Query for a user-friendly, interface-driven approach, and delving into the flexibility of VBA with a User Defined Function for those who prefer a more customized solution.