top of page

Remove all Non-AlphaNumeric Characters Including Spaces and Periods

Here's a follow-up on the Tip of the Night for November 23, 2020, which discussed how to remove non-alphanumeric characters from a cell in Excel. The vba code posted below, available here, will also remove all spaces and periods from the cell. See the comparison of the results in this screen grab, with the results for the below function on in cell B2.

The name of the formula is AlphaNumericOnly. As always, open Visual Basic by pressing ALT + F11, and then insert the vba code in a module by selecting the workbook in the project list on the left, and right clicking on Insert . . . Module.

Function AlphaNumericOnly(strSource As String) As StringDim i As IntegerDim strResult As StringFor i = 1 To Len(strSource)
        Select Case Asc(Mid(strSource, i, 1))
            Case 48 To 57, 65 To 90, 97 To 122: 'include 32 if you want to include space
                strResult = strResult & Mid(strSource, i, 1)
        End SelectNext
    AlphaNumericOnly = strResult
End Function


bottom of page