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




Sean O'Shea has more than 20 years of experience in the litigation support field with major law firms in New York and San Francisco.   He is an ACEDS Certified eDiscovery Specialist and a Relativity Certified Administrator.

The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer.

If you have a question or comment about this blog, please make a submission using the form to the right. 

Your details were sent successfully!

© 2015 by Sean O'Shea . Proudly created with Wix.com

bottom of page