excelvba remove all non alphanumeric characters from a string code example

Example: excel vba remove all non alphanumeric characters from a string

'VBA function to clean a text string so that only alpha-numeric 
'characters and spaces and periods remain:

Function AlphaNumeric$(s$, Optional KeepPattern = "[A-Z.a-z 0-9]")
    Dim i&, token$
    For i = 1 To Len(s)
        token = Mid(s, i, 1)
        If token Like KeepPattern Then BuildString token
    Next
    AlphaNumeric = BuildString(Done:=True)
End Function

Function BuildString(Optional txt$, Optional adjust&, Optional Done As Boolean, Optional Size = "5e6")
    Static p&, s$
    If Done Then BuildString = Left(s, p - 1): p = 0: s = "": Exit Function
    If p = 0 Then: p = 1: s = Space(Size)
    If Len(p) Then p = p + adjust
    Mid$(s, p, Len(txt)) = txt
    p = p + Len(txt)
End Function

'--------------------------------------------------------------------

MsgBox AlphaNumeric("abc &*&$@@~ 1__\/2??~3")   '<--displays: abc 123

'Note: You can optionally pass a different 'KeepPattern' to adjust
'what the function keeps and discards.

Tags:

Vb Example