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.

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.

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.

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

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.

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"})

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.

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

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

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 Code, Microsoft Visual Basic for Applications window pops up.

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.

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

 

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)

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.