Excel: Remove Non-Alphanumeric Characters with Macro

If you’re looking for a formula to replace non-alphanumeric characters there are several combinations of functions that you can use to strip unwanted characters from entire columns. Right off the bat, I’m going to steer you in the direction of using a macro to do the job. It will be much faster, especially if you’re planning on stripping unwanted characters from thousands of cells. If you’ve never used an Excel macro before it is very simple, and you can in fact create your own Excel formula with a VB macro that you can use like any other, like =sum(A2:A5) or =count(A3:A9).

If you haven’t done so already, enable the Developer toolbar in Excel and then click the Macro button to create a new macro. The macro below can be run in the Visual Basic Editor to quickly strip cells of non-alphanumeric characters, or incorporated into another function.

Sub CleanAll()
    Dim rng As Range
    Application.ScreenUpdating = False ' Turns off screen updating
    On Error Resume Next ' Macro will continue if it encounters #N/A
    'Adjust "Sheet1" and Range in Line 6
    For Each rng In Sheets("Sheet1").Range("A1:A10").Cells
        rng.Value = AlphaNumericOnly(rng.Value) ' Function call
    Next
    Application.ScreenUpdating = True
 End Sub

Function AlphaNumericOnly(strSource As String) As String
    Dim i As Integer
    Dim strResult As String
    On Error Resume Next
    For i = 1 To Len(strSource)
    Select Case Asc(Mid(strSource, i, 1))
    'The numbers below match Char Codes to keep
     Case 32, 35 To 47, 48 To 57, 63, 65 To 90, 92, 96, 97 To 122:
        strResult = strResult & Mid(strSource, i, 1)
    End Select
    Next
    AlphaNumericOnly = strResult
 End Function

Line 19 is the most important. It’s in line 19 that you tell Excel which characters you want to keep, not the ones you want to remove. It’s easier this way since the number of alphanumeric characters is well-defined, but the number of characters you want removed is not. Excel has numbers to represent each character, so each number or range of numbers in line 19 represents a character or characters, shown below. There is a complete list of character codes over at Microsoft Office’s site. Note that you also need to update the Sheet name and range of cells that you want to run the macro on in line 6.

Once you’re done creating your macro, put your cursor into the Sub CleanAll() section and click “Play” in the VB Editor to run your macro.
[su_table]

[/su_table]