excel vba force array elements to alphanumeric
'VBA function (extremely fast) to force an array column
'to ONLY alpha-numeric characters (with spaces):
Function ForceArrayColumnAlphaNumeric(v, Optional col& = 1)
Dim i&, j&, p&, max&, t&
Dim b() As Byte, res() As Byte, Keep(0 To 255) As Boolean
Const VALS$ = "0123456789 ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
For j = 1 To Len(VALS)
Keep(Asc(Mid$(VALS, j, 1))) = 1
Next
For i = LBound(v) To UBound(v)
p = 0
max = Len(v(i, col))
ReDim res(0 To max)
b = StrConv(v(i, col), vbFromUnicode)
For j = 0 To max - 1
t = b(j)
If Keep(t) Then
res(p) = t
p = p + 1
End If
Next
v(i, col) = StrConv(res, vbUnicode)
Next
End Function
'----------------------------------------------------------------------------------
array = [A1:Z999].Value2
ForceArrayColumnAlphaNumeric array, 3 '<--the 3rd column of array is now cleansed
'NB: Adjust the VALS$ constant to include only the characters you want to keep.
'NB: Array must be 2D. The column cleansed defaults to column 1.