Excel VBA：搜索字符串以查找第一个非文本字符

Cells contain a mixture of characters within a string, such as:

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

Abcdef_8765
QWERTY3_JJHH
Xyz9mnop

Abcdef_8765 QWERTY3_JJHH Xyz9mnop

I need to find the first non A-Za-z character so that I can strip out the subsequent remainder of the string.

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

So the results would be:

结果将是:

Abcdef
QWERTY
Xyz

Abcdef QWERTY Xyz

I know how to do this if I know exactly what character I'm looking for, but I'm not intuitively grasping how to find ANY character other than A-Za-z.

如果我确切地知道我正在寻找什么角色,我知道如何做到这一点,但我并没有直觉地掌握如何找到除A-Za-z之外的任何角色。

Btw, this is intended to be used within a vba solution.

顺便说一句,这是为了在vba解决方案中使用。

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

EDIT:

I've had success with the following...

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

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

Have I stumbled upon a suitable solution, or is this destined to break? I ask only because I look at Doug Glancy's solution and it seems much more substantial. (btw, I have not yet tested Doug's solution)

我偶然发现了一个合适的解决方案,还是注定要破解?我问的只是因为我看了Doug Glancy的解决方案而且看起来更加充实。 (顺便说一下,我还没有测试过Doug的解决方案)

4 个解决方案

#1

The regexp below looks to remove from the first non A-Z character.

下面的正则表达式看起来从第一个非A-Z字符中删除。

Function StrChange(strIn As String) As String
Dim objRegEx As Object

Set objRegEx = CreateObject("vbscript.regexp")
With objRegEx
    .ignorecase = True
    .Pattern = "^([a-z]+)([^a-z].*)"
    .Global = True
     StrChange = .Replace(strIn, "$1")
End With
End Function

#2

Here is a simple way which doesn't use RegEx. I am deliberately not using RegEx as the other two answer are based on RegEx. RegEx is definitely faster but this is almost equally fast. The difference in speed is almost negligible.

这是一种不使用RegEx的简单方法。我故意不使用RegEx,因为另外两个答案是基于RegEx。 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

Usage:

=GetWord(A1)

EDIT:

Followup from comments. Fine tuned the code (Courtesy @brettdj) .

来自评论的后续行动。精细调整代码(Courtesy @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

More Followup.

Here is something which I had never tried before. I did an actual test of my code vs RegXp and I was surprised to see my code was faster than RegXp which I had not anticipated.

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

I tested it on 10k cells and each cell had a string of 2256 of length

我在10k细胞上进行了测试,每个细胞的长度为2256

The string that I put in Cell A1:A10000 is

我放在Cell A1中的字符串:A10000是

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5Rout

Next I ran this test

接下来我运行了这个测试

#3

You can use a simple regular expression to specify a numeral followed by anything and use this function to replace anything that matches that pattern:

您可以使用简单的正则表达式指定数字后跟任何内容,并使用此函数替换匹配该模式的任何内容:

Function Regex_Replace(strOriginal As String, strPattern As String, strReplacement, varIgnoreCase As Boolean) As String
Dim objRegExp As Object

Set objRegExp = CreateObject("vbscript.regexp")
With objRegExp
    .Pattern = strPattern
    .IgnoreCase = varIgnoreCase
    .Global = True
End With

Regex_Replace = objRegExp.Replace(strOriginal, strReplacement)
Set objRegExp = Nothing
End Function

You'd call it like this:

你这样称呼它:

Sub DeleteAfterNums()
Dim cell As Excel.Range

'Change "Selection" to your range
For Each cell In Selection
'"\d.+" is a numeral and whatever follows it
cell.Value = Regex_Replace(cell.Value, "\d.+", "", True)
Next cell
End Sub

#4

Here is a lightweight and fast method that avoids regex/reference additions, thus helping with overhead and transportability should that be an advantage.

这是一种轻量级且快速的方法,可以避免正则表达式/引用添加,从而有助于提高开销和可传输性。

Public Function GetText(xValue As String) As Variant

For GetText = 1 To Len(xValue)
    If UCase(Mid(xValue, GetText, 1)) Like "[!A-Z]" Then GetText = Left(xValue, GetText - 1): Exit Function
Next

GetText = xValue

End Function

This is then called by using GetText("Submission String") from vba or prepended with a "=" from within a cell formula.

然后通过使用来自vba的GetText(“Submission String”)或在单元格公式中使用“=”前缀来调用它。