VBA Function to Remove Non-Alphanumeric Characters

Occasionally, you'll run into data that has been entered with inadvertent carriage returns or other non-alphanumeric characters. Sometimes, these invisible characters can cause mysterious errors in your code. The solution is to use a function to remove any superfluous characters from the data.

The following function removes everything except for the characters indicated between the brackets. You can tweak the accepted character set as needed. The function works by looping through each character in the string and testing each character to see if it matches one of the characters listed between the square brackets.

Function cleanText(strText As String) As String

    Dim valid as String
    Dim test As String
    valid = ""
    test = ""

    For i = 1 To Len(strText)
        test = Mid(strText, i, 1)
        'Only allow characters and spaces, not carriage returns or any other code
        If test Like "[A-Z,a-z,0-9, ,.,/,~,@,#,$,%,^,&,*,(,),_,-,+,= ]" Then
            valid = valid & test
        End If
    Next i
 
    'Return the valid characters
    cleanText = valid

End Function

The cleanText function uses the Visual Basic (VBA) "Like" operator. The Like operator is similar to using Regular Expressions and also has some similarities to the SQL Like operator. You can read more about the VBA Like operator on this page.