Function AlphaNumericOnly(strSource As String) As String
Dim i As Integer
Dim strResult As String
For i = 1 To Len(strSource)
Select Case Asc(Mid(strSource, i, 1))
Case 48 To 57, 65 To 90, 97 To 122: 'include 32 if you want to include space
strResult = strResult & Mid(strSource, i, 1)
End Select
Next
AlphaNumericOnly = strResult
End Function
Sub CleanAll()
Dim rng As Range
For Each rng In Sheets("Sheet1").Range("A1:K1500").Cells 'adjust sheetname and range accordingly
rng.Value = AlphaNumericOnly(rng.Value)
Next
End Sub
Public Function AlphaNumeric(str As String) As String
Dim i As Long
For i = 1 To Len(str)
If InStr(1, "01234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz. ", Mid(str, i, 1)) Then AlphaNumeric = AlphaNumeric & Mid(str, i, 1)
Next
End Function
Function cleanString(str As String) As String
Dim ch, bytes() As Byte: bytes = str
For Each ch In bytes
If Chr(ch) Like "[A-Z.a-z 0-9]" Then cleanString = cleanString & Chr(ch)
Next ch
End Function
Public Function Isalphanumeric(cadena As String) As Boolean
Select Case Asc(UCase(cadena))
Case 65 To 90 'letras
Isalphanumeric = True
Case 48 To 57 'numeros
Isalphanumeric = True
Case Else
Isalphanumeric = False
End Select
End Function
下面是remove函数
Function RemoveSymbols_Enhanced(InputString As String) As String
Dim InputString As String
Dim CharactersArray()
Dim i, arrayindex, longitud As Integer
Dim item As Variant
i = 1
arrayindex = 0
longitud = Len(InputString)
'We create an array with non alphanumeric characters
For i = 1 To longitud
If Isalphanumeric(Mid(InputString, i, 1)) = False Then
ReDim Preserve CharactersArray(arrayindex)
CharactersArray(arrayindex) = Mid(InputString, i, 1)
arrayindex = arrayindex + 1
End If
Next
'For each non alphanumeric character we do a replace
For Each item In CharactersArray
item = CStr(item)
InputString = Replace(InputString, item, "")
Next
End Function
Function AlphaNum(ByVal s As String, Optional info As Boolean = False) As String
'a) group characters into code categories
Dim codes: codes = getCodes(s, info)
'b) check codes returning only alpha nums
Dim i As Long, ii As Long
For i = 1 To UBound(codes)
Dim char As String: char = Mid$(s, i, 1)
Dim okay As Boolean: okay = False
Select Case codes(i)
' AlphaNum: 4=digits, 5=letters
Case Is >= 4: okay = True
' other characters
Case 2 ' allowing space, minus or comma
If InStr(" ,-", char) <> 0 Then okay = True
Case 3 ' allowing plus or point
If InStr(".+", char) <> 0 Then okay = True
End Select
If okay Then ii = ii + 1: codes(ii) = char
Next i
ReDim Preserve codes(1 To ii)
AlphaNum = Join(codes, vbNullString)
End Function
帮助程序函数
第一次
示例呼叫
Dim s As String
s = "Alpha, -8.9 +äæçñöüéêëÿ'!$""#$%&()*/:;<=>?@|¶"
Debug.Print "~~> " & AlphaNum(s, info:=True)
5条答案
按热度按时间dkqlctbz1#
将此函数插入Visual Basic编辑器的新模块中:
现在,您可以将此公式用作用户定义函数,即,如果数据位于单元格
A1
中,则将此公式置于空单元格=AlphaNumericOnly(A1)
中。如果要直接转换大范围,即替换所有非字母数字字符而不离开源,则可以使用另一个VBA例程来完成此操作:
只需将该子函数放入同一个模块中并执行即可。但要注意,这将替换范围中的所有公式。
cs7cruho2#
我一直在寻找一个比我的解决方案更优雅的解决方案。我打算使用ashleedawg的代码,因为它肯定比我的代码更简洁。讽刺的是,我的代码运行速度快了30%。如果速度很重要(假设你有几百万个任务要做),试试这个:
VBA的每一个角落都有惊喜。我从来没有想到这会更快...
e7arh2l63#
下面是一个使用模式匹配从字符串中删除“任何想要的字符”的替代方法。
[A-Z.a-z 0-9]
)之外的所有内容cleanString
函数:更多信息:
Like
运算符创建模式的详细信息,请参阅:2hh7jdfx4#
我已经写了下面的代码,它的工作,因为我测试它,它包括两个函数。第一个检查字符串是否是字母数字和第二个进行替换(它也删除空格)
下面是remove函数
kx1ctssn5#
取得英数字元,包括空格、+-符号和(点)逗号
**
AlphaNum()
**调用的复杂的helper函数getCodes()
将每个字符分为五个类别,其中通过循环到返回的
codes
数组,您可以只获取相关的alphanum字符或允许的符号。帮助程序函数
第一次
示例呼叫
在VB编辑器的即时窗口中显示