SOLVED

Excel VBA VB 5.5 REGEX - help getting count and value

Brass Contributor

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.

 

8 Replies
I'm so sorry but I need to change the OP...there should be no square brackets surrounding "target1"
best response confirmed by rodsan724 (Brass Contributor)
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.

 

 

Thank you so much!

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

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

@rodsan724 

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

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

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

 

HansVogelaar_0-1680096193986.png

1 best response

Accepted Solutions
best response confirmed by rodsan724 (Brass Contributor)
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.

 

 

View solution in original post