Search code examples
excelregexvbaregex-group

Regex pattern for VBA Excel

How can I pull the string that match regex pattern.

String = "Someting 1245:1000, someting 45678:2000, someting 100:1234"

I need only 1245, 45678, 100 just before the ":"

Sub short() 

  Dim RegEx As Object, MyString As String
  Dim match1 As Variant
    Set RegEx = CreateObject("VBScript.RegExp")

    With RegEx
      .Pattern = "^[\d\d\d\d:\d\d\d\d]"
    End With
end sub
Solution

  • You can use

    Sub short()
  Dim RegEx As RegExp, MyString As String
  Dim m As Match, Matches as MatchCollection

  MyString = "Someting 1245:1000, someting 45678:2000, someting 100:1234"
  Set RegEx = New RegExp

  With RegEx
    .pattern = "\d+(?=:\d{4})"
    .Global = True
  End With

  Set Matches = RegEx.Execute(MyString)

  If Matches.Count > 0 Then
    For Each m In Matches
      Debug.Print m.Value
    Next
  End If

End Sub

    See the debug output:

    enter image description here

    Regex details:

    • \d+ - one or more digits
    • (?=:\d{4}) - a positive lookahead that matches a location that is immediately followed with : and four digits

    See the regex demo.