Remove Non-Alphanumeric Characters in Excel

Excel is an effective tool for manipulating and analysing data, however occasionally the data we work with contains undesired characters that might make our duties more difficult. Non-alphanumeric characters can clog up our data and obstruct calculations, sorting, and other operations. Examples include symbols, punctuation marks, and other special characters. This article is made to make the process simple and clear, regardless of whether you are working with untidy text, unstructured data, or simply wish to clean up your information.

Remove Non-Alphanumeric Characters

Here we will first create a VBA module and then select the range of cells to complete the task. So let us see a simple process to learn how you can remove non-alphanumeric characters in Excel.

Step 1

Consider an Excel sheet where you have cells with non-alphanumeric characters, similar to the below image.

First, right-click on the sheet name and select View code to open the VBA application.

Right-click > View Code.

Step 2

Then click on Insert and select Module, then copy the below code into the text box.

Insert > Module > Copy.

Code

Sub RemoveNotAlphasNotNum()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Remove Non Alphanumeric"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
   xOut = ""
   For i = 1 To Len(Rng.Value)
      xTemp = Mid(Rng.Value, i, 1)
      If xTemp Like "[a-z.]" Or xTemp Like "[A-Z.]" Or xTemp Like "[0-9.]" Then
         xStr = xTemp
      Else
         xStr = ""
      End If
      xOut = xOut & xStr
   Next i
   Rng.Value = xOut
Next
End Sub

Step 3

Then click F5 to run the module. Then select the range of cells and click OK to complete the task. Then you will see that non-alphanumeric characters will be removed.

F5 > Select Cells > OK

This is how you can remove non-alphanumeric characters in Excel.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can remove non-alphanumeric characters in Excel to highlight a particular set of data.