excel vba remove all non alphanumeric characters from a string except period and space
'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.