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.
