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

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

提问by 5th4x4

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-zcharacter 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 thanA-Za-z.

如果我确切地知道我在寻找什么字符，我知道该怎么做，但是我没有直观地掌握如何找到除A-Za-z.

Btw, this is intended to be used within a vbasolution.

顺便说一句，这旨在用于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 的解决方案，它似乎更重要。（顺便说一句，我还没有测试道格的解决方案）

采纳答案by brettdj

The regexpbelow looks to remove from the first non A-Zcharacter.

下面的正则表达式看起来从第一个非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, "")
End With
End Function

回答by Siddharth Rout

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. RegExis 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) .

从评论跟进。微调代码（由@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 RegXpwhich 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:A10000is

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

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5Rout

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5Rout

Next I ran this test

接下来我运行了这个测试

回答by Doug Glancy

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

回答by Spere

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") 或在单元格公式中添加“=”来调用它。