Excel VBA VB 5.5 REGEX - help getting count and value

[rodsan724]

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.

[#2]

I'm so sorry but I need to change the OP...there should be no square brackets surrounding "target1"

[#3 · Accepted solution]

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

[#4]

Thank you so much!

[#5]

You're welcome @rodsan724! Glad I could help.

[#6]

@Subodh_Tiwari_sktneer,  is it possible to add these functions to a PowerQuery that I have connected to a database?

[#7]

@rodsan724 

Nope, but Power Query has other options to do the same.

[#8]

Dear SIr,
Clearly seen that this code taken from output of Chat-GPT.
please remove black background & put neat code
Regards,

[#9]

@chirag050675 

The reply by @Subodh_Tiwari_sktneer is from October of 2021. ChatGPT was launched in November of 2022.

The black background is a feature of this forum. That, the line numbers and the formatting are applied if you use the </> button on the toolbar at the top of the box where you compose a post or reply: