excel - 从字母数字字符串中检索字母字符

Question

怎么分手AB2468123与 excel-vba

我尝试了以下方法:

myStr = "AB2468123"
split(myStr, "1" OR "2" OR "3"......."9")

我只想得到字母(字母)。

谢谢。

Answer 1

如何仅从输入字符串中检索字母:

Function GetLettersOnly(str As String) As String
    Dim i As Long, letters As String, letter As String

    letters = vbNullString

    For i = 1 To Len(str)
        letter = VBA.Mid$(str, i, 1)

        If Asc(LCase(letter)) >= 97 And Asc(LCase(letter)) <= 122 Then
            letters = letters + letter
        End If
    Next
    GetLettersOnly = letters
End Function

Sub Test()
    Debug.Print GetLettersOnly("abc123")      // prints "abc"
    Debug.Print GetLettersOnly("ABC123")      // prints "ABC"
    Debug.Print GetLettersOnly("123")         // prints nothing
    Debug.Print GetLettersOnly("abc123def")   // prints "abcdef"
End Sub

编辑:为了完整性(和 Chris Neilsen)这里是 Regex方法:

Function GetLettersOnly(str As String) As String
    Dim result As String, objRegEx As Object, match As Object

    Set objRegEx = CreateObject("vbscript.regexp")

    objRegEx.Pattern = "[a-zA-Z]+"
    objRegEx.Global = True
    objRegEx.IgnoreCase = True

    If objRegEx.test(str) Then
        Set match = objRegEx.Execute(str)
        GetLettersOnly = match(0)
    End If
End Function

Sub test()
    Debug.Print GetLettersOnly("abc123") //prints "abc"
End Sub