SOLVED

Excel VBA VB 5.5 REGEX - help getting count and value

%3CLINGO-SUB%20id%3D%22lingo-sub-2800958%22%20slang%3D%22en-US%22%3EExcel%20VBA%20VB%205.5%20REGEX%20-%20help%20getting%20count%20and%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2800958%22%20slang%3D%22en-US%22%3E%3CP%3EGiven%20the%20following%20contents%20in%20A1%20cell%3A%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3ELog%20file%3CBR%20%2F%3E%5Btarget1%5D%20%3D%201234%3CBR%20%2F%3E%5Btarget1%5D%20%3D%205678%2C%20blah%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20use%20VB%205.5%20Regex%20to%20get%20count%20of%20%22target1%22%20and%20the%20value%20for%20the%20latter%20target1%20value%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EResults%3A%3C%2FP%3E%3CP%3Etarget1%20count%3A%202%3C%2FP%3E%3CP%3Etarget1%20value%3A%205678%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20got%20a%20working%20example%20of%20regex%20in%20Excel%20from%20%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fa%2F22542835%2F139698%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E%2C%20just%20need%20help%20with%20the%20pattern.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2800958%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2801040%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VBA%20VB%205.5%20REGEX%20-%20help%20getting%20count%20and%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2801040%22%20slang%3D%22en-US%22%3EI'm%20so%20sorry%20but%20I%20need%20to%20change%20the%20OP...there%20should%20be%20no%20square%20brackets%20surrounding%20%22target1%22%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2801444%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VBA%20VB%205.5%20REGEX%20-%20help%20getting%20count%20and%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2801444%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1165219%22%20target%3D%22_blank%22%3E%40rodsan724%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20the%20following%20functions...%3C%2FP%3E%3CP%3ETo%20get%20the%20Count%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EFunction%20getTargetCount(ByVal%20str%20As%20String)%20As%20Long%0ADim%20Matches%20As%20Object%0AWith%20CreateObject(%22VBScript.RegExp%22)%0A%20%20%20%20.Global%20%3D%20True%0A%20%20%20%20.ignorecase%20%3D%20True%0A%20%20%20%20.Pattern%20%3D%20%22target1%20%3D%20(%5Cd%2B)%22%0A%20%20%20%20If%20.test(str)%20Then%0A%20%20%20%20%20%20%20%20Set%20Matches%20%3D%20.Execute(str)%0A%20%20%20%20%20%20%20%20getTargetCount%20%3D%20Matches.Count%0A%20%20%20%20Else%0A%20%20%20%20%20%20%20%20getTargetCount%20%3D%200%0A%20%20%20%20End%20If%0AEnd%20With%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20get%20the%20Last%20Value%20of%20Target%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EFunction%20getTargetValue(ByVal%20str%20As%20String)%20As%20Long%0ADim%20Matches%20As%20Object%0AWith%20CreateObject(%22VBScript.RegExp%22)%0A%20%20%20%20.Global%20%3D%20True%0A%20%20%20%20.ignorecase%20%3D%20True%0A%20%20%20%20.Pattern%20%3D%20%22target1%20%3D%20(%5Cd%2B)%22%0A%20%20%20%20If%20.test(str)%20Then%0A%20%20%20%20%20%20%20%20Set%20Matches%20%3D%20.Execute(str)%0A%20%20%20%20%20%20%20%20getTargetValue%20%3D%20Matches(Matches.Count%20-%201).submatches(0)%0A%20%20%20%20End%20If%0AEnd%20With%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20you%20may%20either%20use%20these%20functions%20in%20another%20code%20or%20as%20regular%20Excel%20functions%20on%20the%20worksheet%20itself.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2803944%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VBA%20VB%205.5%20REGEX%20-%20help%20getting%20count%20and%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2803944%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2804944%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VBA%20VB%205.5%20REGEX%20-%20help%20getting%20count%20and%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2804944%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1165219%22%20target%3D%22_blank%22%3E%40rodsan724%3C%2FA%3E!%20Glad%20I%20could%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2805847%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VBA%20VB%205.5%20REGEX%20-%20help%20getting%20count%20and%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2805847%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%2C%26nbsp%3B%20is%20it%20possible%20to%20add%20these%20functions%20to%20a%20PowerQuery%20that%20I%20have%20connected%20to%20a%20database%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.

 

6 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 (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.