Forum Discussion

rodsan724's avatar
rodsan724
Brass Contributor
Sep 30, 2021

Excel VBA VB 5.5 REGEX - help getting count and value

Given the following contents in A1 cell:  

Log file
, target1 = 1234, blah
, target1 = 5678, blah

 

Is there a way to use VB 5.5 Regex to get count of "target1" and the value for the latter target1 value?

 

Results:

target1 count: 2

target1 value: 5678

 

I've got a working example of regex in Excel from here, just need help with the pattern.

 

Excel
Formulas and Functions
Macros and VBA
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Oct 01, 2021

    rodsan724 

    You may try the following functions...

    To get the Count:

    Function getTargetCount(ByVal str As String) As Long
Dim Matches As Object
With CreateObject("VBScript.RegExp")
    .Global = True
    .ignorecase = True
    .Pattern = "target1 = (\d+)"
    If .test(str) Then
        Set Matches = .Execute(str)
        getTargetCount = Matches.Count
    Else
        getTargetCount = 0
    End If
End With
End Function

     

    To get the Last Value of Target:

    Function getTargetValue(ByVal str As String) As Long
Dim Matches As Object
With CreateObject("VBScript.RegExp")
    .Global = True
    .ignorecase = True
    .Pattern = "target1 = (\d+)"
    If .test(str) Then
        Set Matches = .Execute(str)
        getTargetValue = Matches(Matches.Count - 1).submatches(0)
    End If
End With
End Function

     

    Then you may either use these functions in another code or as regular Excel functions on the worksheet itself.

     

     

  • rodsan724's avatar
    rodsan724
    Brass Contributor
    Sep 30, 2021
    I'm so sorry but I need to change the OP...there should be no square brackets surrounding "target1"
    • Subodh_Tiwari_sktneer's avatar
      Subodh_Tiwari_sktneer
      Silver Contributor
      Oct 01, 2021

      rodsan724 

      You may try the following functions...

      To get the Count:

      Function getTargetCount(ByVal str As String) As Long
Dim Matches As Object
With CreateObject("VBScript.RegExp")
    .Global = True
    .ignorecase = True
    .Pattern = "target1 = (\d+)"
    If .test(str) Then
        Set Matches = .Execute(str)
        getTargetCount = Matches.Count
    Else
        getTargetCount = 0
    End If
End With
End Function

       

      To get the Last Value of Target:

      Function getTargetValue(ByVal str As String) As Long
Dim Matches As Object
With CreateObject("VBScript.RegExp")
    .Global = True
    .ignorecase = True
    .Pattern = "target1 = (\d+)"
    If .test(str) Then
        Set Matches = .Execute(str)
        getTargetValue = Matches(Matches.Count - 1).submatches(0)
    End If
End With
End Function

       

      Then you may either use these functions in another code or as regular Excel functions on the worksheet itself.

       

       

Resources