VBA Regex Pattern

[decadence]

Hi, can someone tell me the pattern for extracting variable length of letters starting from an alphanumeric string, The string will always start with letters first.

Here is the Code I am Using

Private Sub Test()
    Dim Rng As Range, Fnd As Range, x As Range
    Dim strPattern As String, strInput As String
    Dim RegEx As Object
    Dim Arr As Variant
    Dim i As Integer
    
    Arr = Array("References", "Reference", "Ref's", "Refs", "Ref")
        For i = LBound(Arr) To UBound(Arr)
            Set Fnd = ActiveSheet.Columns.Find(What:=Arr(i), LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
        If Not Fnd Is Nothing Then
            Set Rng = Range(Fnd.Offset(1), Cells(Rows.Count, Fnd.Column).End(xlUp))
        End If
    Next i
    
    Set RegEx = CreateObject("VBScript.RegExp")
    For Each x In Rng
    
        'strPattern =  <---------

        If strPattern <> "" Then
            strInput = x.Value

    With RegEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = True
                .Pattern = strPattern
            End With

            If RegEx.Test(strInput) Then
                x.Offset(0, 7) = RegEx.Replace(strInput, "$1")
            Else
                x.Offset(0, 7) = "(Not matched)"
            End If
        End If
    Next x
End Sub

 

[Yongle]

Try this

"[A-Za-z]+"

If expected values not returned, provide 10 different (typical) search strings highlighting what regex must find

 

[Yongle]

This function (can use like a formula) returns the first alpha string

Function FirstAlphaString(ByVal aRange As String) As String
    With CreateObject("vbscript.regexp")
        .Global = True: .MultiLine = True: .IgnoreCase = True
        .Pattern = "[A-Za-z]+"
        FirstAlphaString = .Execute(aRange)(0)
    End With
End Function

 

[decadence]

Hi Yongle, Thanks for the reply, didn't quite work so I used this and it works "[^A-Za-z]+". Also what pattern would I need to use to get the last part of a string after an underscore.

Examples:
This_Apple01
the regex pattern would return Apple01

I_Like_30_Oranges
the regex pattern would return Oranges

 

[decadence]

Solved it without regex pattern, Thanks for your help

 

[Rick Rothstein]

Solved it without regex pattern, Thanks for your help

You did not post your non-RegExp solution, so we don't know what you came up with. For anyone reading this thread in the future, here is a function that would return the text after the last underscore character...

[table="width: 500"]
[tr]
	[td]Function AfterUnderscore(S As String) As String
  AfterUnderscore = Mid(S, InStrRev(S, "_") + 1)
End Function[/td]
[/tr]
[/table]

The above function returns the whole text if there is not underscore character in it. The other possibility is to return the empty string ("") if there is no underscore; here is a function to do it that way...

[table="width: 500"]
[tr]
	[td]Function AfterUnderscore(S As String) As String
  If S Like "*_*" Then AfterUnderscore = Mid(S, InStrRev(S, "_") + 1)
End Function[/td]
[/tr]
[/table]

 

[decadence]

Apologies Rick, but I was very busy at that time so I just posted a quick response.

This is what I used.

Dim str As String, x As range
For Each x In Selection
    str = x.Offset(0, 2).Value
    x.Offset(0, 3).Value = [COLOR=#0000ff]Right(str, Len(str) - (InStrRev(str, "_")))[/COLOR]
Next x
End Sub

Out of curiosity is there a Regex Pattern that will do this as the underscore is tied in with Letters rather than Special characters

 

[Rick Rothstein]

Apologies but I was very busy at that time so I just posted a quick response

This is what I used.

Dim str As String, x As range
For Each x In Selection
    str = x.Offset(0, 2).Value
    x.Offset(0, 3).Value = [B][COLOR="#FF0000"]Right(str, Len(str) - (InStrRev(str, "_")))[/COLOR][/B]
Next x
End Sub

In VB, the third argument is optional... when omitted, it makes the function return the rest of the text starting at the position specified in the second argument. Given that, you can replace what I highlighted in red with this much simpler function call (which I used in the code I posted in Message #6 )...

Mid(str, InStrRev(str, "_") + 1)

By the way, str is not a good name to use for a variable as it is also the name of a built-in VBA function.

 

[Rick Rothstein]

Out of curiosity is there a Regex Pattern that will do this...

Probably, but I don't know for sure as I do not use Regular Expressions (always found that whatever could be done with them can also be done without them). By the way, I am sorry I did not address this question in my previous response... I completely missed it for some reason.

 

[Peter_SSs]

Out of curiosity is there a Regex Pattern that will do this as the underscore is tied in with Letters rather than Special characters

Try this. It could be written a bit more concisely but I have tried to stick basically to your structure.

Sub AfterLastUnderscore()
  Dim s As String
  Dim x As Range

  With CreateObject("VBScript.RegExp")
    .Pattern = "_[^_]+$"
    For Each x In Selection
      s = x.Offset(0, 2).Value
      If .Test(s) Then x.Offset(0, 3).Value = Mid(.Execute(s)(0), 2)
    Next x
  End With
End Sub

Further comments:
- As well as Rick's comment about using str as a variable, I'm also wondering about your use of 'Selection'. It is rare to need to select a range in vba to work with it and selecting can slow your code considerably.

- I don't know much about your data, but if a cell has no underscore in it your code from post 7 returns what was originally in the cell. My code above returns nothing as to my mind there is nothing after the last underscore. If you can have that situation and want the same result as your code with RegExp, post back for a modification.