regex - Excel VBA : search a string to find the first non-text character

Question

单元格包含字符串中的混合字符，例如:

Abcdef_8765
QWERTY3_JJHH
Xyz9mnop

我需要找到第一个非 A-Za-z 字符，以便我可以去掉字符串的后续剩余部分。

所以结果是:

Abcdef
QWERTY
Xyz

如果我确切地知道我要查找的字符，我就知道该怎么做，但我无法直观地掌握如何查找除 A-Za-z 之外的任何字符。

顺便说一句，这旨在在 vba 解决方案中使用。

====================

编辑:

我在以下方面取得了成功...

a = "abc123"
b = Len(a)

For x = 1 To b

c = (Mid(a, x, 1) Like "[a-zA-Z]")
If c = False Then
d = Left(a, x - 1)
Exit Sub
End If

Next x

我是否偶然发现了合适的解决方案，或者这注定会失败？我问这个问题只是因为我看了 Doug Glancy 的解决方案，它看起来更实质性。(顺便说一句，我还没有测试道格的解决方案)

Answer 1

这是一种不使用正则表达式的简单方法。我故意不使用正则表达式，因为其他两个答案都基于正则表达式。 RegEx 肯定更快，但这几乎同样快。速度差异几乎可以忽略不计。

Function GetWord(Rng As Range)
    Dim i As Long, pos As Long

    For i = 1 To Len(Rng.Value)
        Select Case Asc(Mid(Rng.Value, i, 1))
            Case 65 To 90, 97 To 122
            Case Else: pos = i: Exit For
        End Select
    Next i

    GetWord = Left(Rng.Value, pos - 1)
End Function

用法:

=GetWord(A1)

编辑:

评论跟进。微调代码(由 @brettdj 提供)。

Function GetWord(Rng As Range)
    Dim i As Long, pos As Long
    Dim sString As String

    sString = UCase$(Rng.Value)

    For i = 1 To Len(sString)
        Select Case Asc(Mid$(sString, i, 1))
        Case 65 To 90
        Case Else: pos = i: Exit For
        End Select
    Next i

    GetWord = Left(Rng.Value, pos - 1)
End Function

更多跟进。

这是我以前从未尝试过的东西。我对我的代码与 RegXp 进行了实际测试，我惊讶地发现我的代码比 RegXp 更快，这是我没有预料到的。

我在 10k 个单元上进行了测试，每个单元都有一个长度为 2256 的字符串

我放入单元格A1:A10000的字符串是

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5Rout

接下来我运行了这个测试