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

 

[decadence]

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.

Hi Rick I didn't realise that, Thanks for the Info and your help.

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.

Hi Peter, the reason I am using selection is because the data varies in every workbook and each line in each book varies so some lines won't need extracting the last word, I do a lot of data editing to create a modified versions of workbooks for my company to use.

 

[Peter_SSs]

So did the Regex pattern work as you wanted?

 

[decadence]

Hi Peter Yes it does thank you very much.

 

[Peter_SSs]

Hi Peter Yes it does thank you very much.

OK, good. Thanks for the follow up.