Find Year, InvoiceNo, (0-3)OrderNo in a String with VBA, Regexp (MS VBScript 5.5)

Leonhard Körber 0 Reputation points
2023-05-19T10:07:49.2333333+00:00

In a String Like this:

I want top find Year, InvoiceNo, (0-3)OrderNo in the String with VBA, Regexp (MS VBScript 5.5)

Year starts with "in_"(4 Digit)

Invoice follows with (4 or 5 Digit) seperated with ( _ oder - )

The OrderNumber follows 0 or 3 Times seperated with ( _ oder - )

OrderNumber follows with (5 or 6 Digit) seperated with ( _ oder - )

After the Last OrderNumber can be add Text vor explanations with or without Numbers inside,before or after.

"in_2023_1234-567890_12345-67890_(Text with Numbers etc. top ignore)"
"in_2023_1234-567890_12345-67890_(Text with Numbers etc. top ignore)"
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,555 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Viorel 111.5K Reputation points
    2023-05-19T11:27:11.1033333+00:00

    Check an example:

    Dim example As String
    example = "in_2023_1234-567890_12345-67890_(Text with Numbers etc. top ignore)"
    
    Dim re As New RegExp
    re.Pattern = "^in_(\d{4})[_\-](\d{4,5})(?:[_\-](\d{5,6}(?:[_\-]\d{5,6}){0,2}))?[_\-]"
    
    Dim matches As MatchCollection
    Set matches = re.Execute(example)
    
    If matches.Count > 0 Then
    
        Dim m As Match
        Set m = matches(0)
        
        Dim year As Integer
        Dim invoiceNo As String
        Dim orderNo As String
        
        year = CInt(m.SubMatches(0))
        invoiceNo = m.SubMatches(1)
        orderNo = m.SubMatches(2)
        
        ' . . .
    End If
    
    0 comments No comments