Capturing the submatches after the second instance of a string variable

Viewed 93

How would one capture two numeric submatches after the second instance of a string/number?

I have a # that changes from .txt file to .txt file. It is captured in a variable called "Total" which I declared as a string. The string contains numbers, in the format of 123, 456,789.23 or 123, 456.01. This number appears about 3 times within the .txt file, and I have written a RegEx pattern that is able to capture the first instance of this number and its submatches.

regex.Pattern =  Total & "\s*([\d+\.\d*])\s*([\d+\.\d*])\s*"

The .txt file portion I am trying to capture may appear as

123,456,789.38 

2.180
 
251.517

OR

123,456,789.38  2.180  251.517 

I want to capture 2.180 and 251.517.

The first instance includes the words "Number of: " in front of it, and I tried to make the pattern avert from the ":" before it by writing:

regex.Pattern = "[^:\s]" & Total & "\s*([\d+\.\d*])\s*([\d+\.\d*])\s*"

It still picks up this first instance and the numbers after that first instance. The second instance does not have any defining words before it, just a blank line such as the one below:

123,456,789.38 

2.180 

251.517 

Additional information:

    Dim regex As Object: Set regex = CreateObject("vbscript.regexp")
    regex.Pattern =  Total & "\s*([\d+\.\d*])\s*([\d+\.\d*])\s*"
    Dim MCS As Object
    Set MCS = regex.Execute(myText)
    Dim Total As String: Total = MCS(0).submatches(0)
    Dim submatch1 As String: submatch1 = MCS(0).Submatches(1)
    Dim submatch2 As String: submatch2 = MCS(0).Submatches(2)

where mytext is the contents of the .txt file entirely as a string.

There are also words and numbers between the different instances of the variable "Total", such as

Number of: 123,456,789.38

Text Here Text Here

Number Here

123,456,789.38

2.180

251.517

I am also not sure how much/many text/numbers there will be between the first two instances of 123,456,789.38, so I am trying to think of how to work this to be flexible.

When I mention second instance, I mean that the number 123,456,789.38 (which is the variable named "Total") appears three times in the document. I want to capture the two submatches that appear after that number. However, since there are three times it appears, I want to capture the two submatches that appear after the second time that 123,456,789.38 pops up.

Link to the text file.
https://regex101.com/r/4hPtY3/6

Output:

submatch1 = 2.180
submatch2 = 251.517

Currently, it is capturing

submatch1 = 97
submatch2 = 5

with the pattern:

regex.Pattern = Replace(Total, ".", "\.") & "\s*([.\d]+)\s*([.\d]+)"
1 Answers

You can use

regex.Pattern = "^(?:[\s\S]*?" & Replace(Total, ".", "\.") & "){2}\D+([.\d]+)\s*([.\d]+)"

Here is the regex demo where you can see that

enter image description here

As expected.

Details

  • ^ - start of string
  • (?:[\s\S]*?123,456\.78){2} - two occurrences of any 0+ chars as few as possible and then 123,456.78 string
  • \D+ - 1 or more non-digit chars
  • ([.\d]+) - Group 1: one or more dots or digits
  • \s* - 0+ whitespaces
  • ([.\d]+) - Group 2: one or more dots or digits.
Related