VBA RegEx: How to find the first instance of a number after a specific string and ignore all other characters?

user_14580441 0
Viewed 90

Having trouble with writing code that will pick up the pattern I want. I want to be able to grab the first number that comes up after the words 5 Months in the .txt file that I have. If there are any other characters A-Z, parentheses, $, % etc. I want to ignore them. I keep getting an error code with VBA such as the INVALID PROCEDURE CALL OR ARGUMENT.

Currently, I have code that looks like this:

Dim reg4 As Object: Set reg4 = CreateObject("vbscript.regexp")
 reg4.Pattern = "5 Months\s*([\d+]\.[\d+])\s*"
   Dim MCS As Object
    Set MCS = reg4.Execute(myText)
    **Dim Months5 As String: Months5 = MCS(0).submatches(0)** *the error stems from this line*

where mytext is a string that consists of content from a text file. My main problem is that this text file is not always in a standardized format, so when I want to extract the first number after "5 Months" it gives me that error.

The text file could look like:

EXAMPLE 1

5 Months

($) (%) (Months) (%) (%) (%) ($) (Months)

0.00 0.0000 0.000

OR EXAMPLE 2

5 Months

0.00

0.000

0.000

In both cases, I would ideally be able to extract that first number "0.00" in its entire form, while ignoring any other characters such as (%) or ($) as shown in example 1.

I would like to ask if anyone has any suggestions on how to rewrite the pattern statement so it will be able to pick up the first numeric instance along with the numbers after its decimal point?

Many thanks in advance!

vba regex
1 Answers

Your regex does not match the strings you showed. You can use

\b5 Months[\s\S]*?(\d+(?:\.\d+)?)

See the regex demo. Details:

  • \b - a word boundary
  • 5 Months - a literal text
  • [\s\S]*? - any 0 or more chars, as few as possible
  • (\d+(?:\.\d+)?) - Capturing group 1: one or more digits followed with an optional sequence of a . and one or more digits.

Test run in VBA:

Sub TestFn()
Dim reg4 As Object: Set reg4 = CreateObject("vbscript.regexp")
 reg4.Pattern = "\b5 Months[\s\S]*?(\d+(?:\.\d+)?)"
Dim myText As String
 myText = "5 Months" & vbCrLf & vbCrLf & "0.00"
Dim MCS As Object
Set MCS = reg4.Execute(myText)
Dim Months5 As String: Months5 = MCS(0).SubMatches(0)
Debug.Print (Months5)
End Sub

enter image description here

user_38329700
Related