当前位置: 动力学知识库 > 问答 > 编程问答 >

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

问题描述:

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

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.

So the results would be:

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.

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

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

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)

网友答案:

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

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
网友答案:

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.

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

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.

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

The string that I put in Cell A1:A10000 is

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5RoutaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccdddddddddddddddddddddddddddddddddddddddddddddddddddddddddeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeSiddharth5Rout

Next I ran this test

网友答案:

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
网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: