3 Ways to Remove Non-Alphanumeric Characters in Excel

Numbers, alphabets, and non-alphanumeric characters often get mixed up in huge volume of data and we often need to remove non-alphanumeric characters in Excel. In this article, you will get some idea about how you can remove such non-alphanumeric characters in Excel by 3 different approaches.

📁 Download Excel File

Download the practice workbook below.

What Are Non-Alphanumeric Characters?

Non-alphanumeric characters are any characters or symbols other than letters and numbers that may be typed on a computer keyboard. These special characters include punctuation and mathematical symbols. For example, # is a non-alphanumeric character.

Learn to Remove Non-Alphanumeric Characters in Excel with These 3 Approaches

Suppose you have some data where non-alphanumeric symbols are mixed with the alphabets and numbers and you want to get rid of such symbols. Solving this problem can be quite tricky and most of the time you might require a code to solve such a problem. In this article, we will be talking about the processes available to remove Non-Alphanumeric characters in Excel. The dataset we will be used to illustrate things clearly is as follows:

We can use the SUBSTITUTE function in Excel to replace alphanumeric characters with empty strings. The syntax of the SUBSTITUTE Function is as follows:

Text (Required) : The reference of the cell containing text which we want to replace.

Old_text (Required): The text we want to replace.

New_text (Required): The text we want to replace the old text with.

Instance_num (Optional): This specifies which instance of old text we want to replace with new_text.

We should remember that the SUBSTITUTE Function replaces one particular non-alphanumeric character. So, depending on the dataset we have to use the function multiple times if we need to do so. The steps for this process are as follows:

 ⬇️⬇️ STEPS ⬇️⬇️

• We select cell C6.
• After that, we hover over to the formula tab and write the following formula.

• Now we go to the bottom right of cell C6 and drag the Fill Handle icon down to get the results for the other data.

• The final image free of alphanumeric characters will look like the one below:

📕 Read More: 5 Ways to Remove Last Character from String in Excel

We cannot use the SUBSTITUTE Function when we need to remove a large number of non-alphanumeric characters from a dataset. Let us include more alphanumeric characters inside the existing dataset and now take a different approach. In this approach, we will be making a user-defined function in the Developer section. The steps are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• We go to the Developer tab and click on Visual Basic. Alternatively, we can press the shortcut key Alt+F11.

• We go to Insert and click on Module.

• A new Module will be created. We write the following code on the blank box on the right. Here, we have made a new function named RemoveNonAlphaNumericCharacters() and later called the function to filter the cell values from Non-Alphanumeric Characters.

Function RemoveNonAlphaNumericCharacters(str As String) As String
Dim strR As String
Dim cha As String
Dim strmd As String
Dim inte As Integer
strmd = "[A-Z.a-z 0-9]"
strR = ""
For inte = 1 To Len(str)
cha = Mid(str, inte, 1)
If cha Like strmd Then
strR = strR & cha
End If
Next
RemoveNonAlphaNumericCharacters = strR
End Function

• We run the code. Before it is executed, a pop-up box with the name Macro will come. We write a short form for User Defined Function as UDF and press Create.

• After that, the visual basic window will look like this.

• Now we come back to cell C6 and write the following formula.

• The non-alphanumeric characters will be removed.
• After that, we drag the Fill Handle icon down to get all the results.

• The overall output will look like the following image:

📕 Read More: 3 Ways to Remove Non-Numeric Characters from Cells in Excel

Another approach to solving this problem is to insert a VBA code in the Visual Basic module in Excel. To demonstrate this approach, we need to follow these steps:

⬇️⬇️ STEPS ⬇️⬇️

• First, we copy the contents of the Non-Alphanumeric Data Column present in cell range B6:B12 to the Clean Data Column in cell range C6:C12.

• Then we select the cells C6:C12 and go to the Developer Options tab and select Visual Basic or alternatively press Alt+F11 as a shortcut.

• A new window will appear. We go to Insert and then click on Module.

• In Module 2, we write the following VBA Code.

Sub VBAtoRemoveNonAlphanumericCharacters()
Dim R As Range
Dim WR As Range
On Error Resume Next
yTitleId = "Non-alphanumericcharactersremove"
Set WR = Application.Selection
Set WR = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each R In WR
yOut = ""
For i = 1 To Len(R.Value)
yTemp = Mid(R.Value, i, 1)
If yTemp Like "[a-z.]" Or yTemp Like "[A-Z.]" Or yTemp Like "[0-9.]" Then
yStr = yTemp
Else
yStr = ""
End If
yOut = yOut & yStr
Next i
R.Value = yOut
Next
End Sub

• We Run the code or press the F5 shortcut to run the code.

• A popup box of Macros will appear. We select the Macro name as shown in the picture and press Run.

• The results will turn out like the image shown below.

📄 Important Notes

🖊️  If we cannot find the Developer tab, we can activate it by going to Customized Quick Access Toolbar and then clicking More Commands. After that, we select Developer from Customize Ribbon and then press OK.

🖊️  The SUBSTITUTE Function will basically not work on cases where we need to get rid of multiple non alphanumeric characters. We need to call the function as many times as there is the number of different non alphanumeric characters.

🖊️  Creating a custom formula does not alter the structure of a worksheet or a cell. It has limitations.

📝 Takeaways from This Article

You have taken the following summed-up inputs from the article:

📌  We can use the SUBSTITUTE Function to get rid of non-alphanumeric characters but it has to be used as much as the number of individual non-alphanumeric character present.

📌  Custom Formulas can also remove non-alphanumeric characters and we can call such functions anytime after creating one.

📌  Finally, using VBA Code is the most accepted form by which we can remove non-alphanumeric characters very easily.

Conclusion

In conclusion, I hope this article was able to provide you with insights to remove non-alphanumeric characters in Excel. If you have any queries regarding the topic, feel free to ask in the comment section. Visit our site Excelden to get more informative articles related to Excel.

Related Articles

• 7 Quick Tricks to Remove Apostrophe in Excel
• 4 Ways to Remove Last 3 Characters in Excel
• 6 Ways to Remove Blank Characters in Excel