VBA:获取字符串中的字母或数字

方法一:

Sub allenglish()
Dim rng As Range, a As Range
Set rng = Application.InputBox("请选择单元格区域", "提取单元格的中文", , , , , , 8)
For Each a In rng
MyStr = a.Value
ResultStr = ""
With CreateObject("VBSCRIPT.REGEXP")
.Pattern = "[a-zA-Z0-9]"
.IgnoreCase = True
.Global = True
If .test(MyStr) Then
For Each Item In .Execute(MyStr)
ResultStr = ResultStr & Item
Next Item
a.Offset(0, 1) = ResultStr
End If
End With
Next a
End Sub

注意:vb语句中的第8行:

[a-zA-Z0-9]:取指定区域单元格中英文字母及数字;

[a-zA-Z]:取指定区域单元格中英文字母;

[一-龟]:取指定区域单元格中所有中文;

方法二:自定义函数代码:截取字符串中的汉字、数字、字母

Function MyGet(Srg As String, Optional n As Integer = False)
 'n为1,取汉字,n为2,取字母,n为0,取数字
Dim i As Integer
Dim s, MyString As String
Dim Bol As Boolean
For i = 1 To Len(Srg)
s = Mid(Srg, i, 1)
If n = 1 Then
Bol = Asc(s) < 0 '文字
ElseIf n = 2 Then
Bol = s Like "[a-z,A-Z]" '字母
ElseIf n = 0 Then
Bol = s Like "#" '数字
End If
If Bol Then MyString = MyString & s
Next
MyGet = IIf(n = 1 Or n = 2, MyString, Val(MyString))
End Function

方法三:【感觉比方法2好用】

Function MyGet(Srg As String, Optional n As Integer = False)
    Dim i As Integer
    Dim s, MyString As String
    Dim Bol As Boolean
    
    For i = 1 To Len(Srg)
        s = Mid(Srg, i, 1)
        If n = 1 Then
            Bol = Asc(s) < 0
        ElseIf n = 2 Then
            Bol = s Like "[a-z,A-Z, ,.,_,*,$,/,+,-]"
        ElseIf n = 0 Then
            Bol = s Like "[0-9,.,-,*,$,/,+,-]"
        End If
        If Bol Then MyString = MyString & s
    Next
    MyGet = MyString
End Function