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.

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

x
How to Select Every Other Row in Excel

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

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

Related Posts

VBA Macro For VLOOKUP From Another Sheet
vba macro for vlookup from another sheet1

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you how to use VBA code to vlookup data ...

How To Insert Comments in Protected Worksheet in Excel
insert commetns in protected workshee3

This post will show you how to allow comments in a protected worksheet in Excel. You can easily to insert comments into cells in a normal worksheet in Excel, but if want to insert a comment in a worksheet that ...

How To Convert Text to Upper Cases(Using VBA) in Excel
convert text to upper cases1

This post will show you how to switch from lower case to upper case in Excel. and I am going to show you two different ways of converting text to upper cases using formula or VBA macro in Excel 2013,Excel ...

How To Hide Every Other Row in Excel (Using VBA)
hide every other row1

This post will show you how to hide alternate rows or columns in Excel or how to hide every third, fourth, fifth row or column in Excel. If you want to hide every other row in your current worksheet, how ...

How to Disable the Save As Prompt in Excel
disable save as prompt1

This post will show you how to use a VBA Macro to save an Excel file and overwrite any existing file without a prompt so that you are going to get the little window that says file already exists do ...

How to Count Cells that Contain even or odd numbers in Excel
count cells that contain even numbers5

This post will guide you how to count the number of cells that contain odd or even numbers within a range of cells using a formula in Excel 2013/2016.How do I count cells that contain odd numbers through the use ...

How to Count Cells Are Not Blank or Empty in Excel
count non blank nonempty cells5

This post will guide you how to count cells that are not blank or empty in a given range cells using a formula in Excel 2013/2016.How do I count the number of cells that are not blank in a particular ...

How to Count Cells Less Than a Specific Value in Excel
count cells lessr than5

This post will guide you how to count the number of cells less than a particular numeric value in a given range cells using a formula in Excel 2013/2016. How do I count cells that are less than a specific ...

How to Count Cells Greater Than a Specific Value in Excel
count cells greater than 5

This post will guide you how to count the number of cells greater than a particular numeric value in a given range cells using a formula in Excel 2013/2016. How do I count cells that are greater than a specific ...

How to Remove All Extra Spaces and Keep Only One Between Words in Excel
How to Remove All Extra Spaces and Keep Only One Between Words 7

Sometimes when copying something from other type files to excel, there might be two or more spaces display between words. Extra spaces between words are frequently to be seen in excel, and this behavior is very annoying. If we want ...

Sidebar