Geek asks and answers

Vasscript Regex Fill Submatches, even if not required for a match

I am trying to replicate a Google calendar method to create an appointment from a storytelling. I want to introduce 5pm Happy Hour for 1 hourand analyze it, ultimately, Outlook AppointmentItem.

My problem, I think I have a big chunk of additional text at the end. And since this is not necessary, the regular expression passes, but the swap is not populated, because it is not required to match. I want it populated because I want to use submatrices as my parser.

I have a bunch of test cases in column A (works in Excel and then moves to Outlook), and in my code the submatrices on the right are listed. This is a representative sample of potential input.

1. 5pmCST Happy Hour for 1 hour
2. 5pm CST Happy Hour for 1 hour
3. 5pm Happy Hour for 1 hour
4. 5 pm Happy Hour for 1 hour
5. 5 pm CST Happy Hour for 1 hour
6. 5 Happy Hour for 1 hour
7. 5 Happy Hour
8. 5pmCST Happy Hour
9. 5pm CST Happy Hour
10. 5pm Happy Hour
11. 5:00CST Happy Hour for 1 hour
12. 5:00 CST Happy Hour for 1 hour

Here is the code that runs the tests

Sub testest()

    Dim RegEx As VBScript_RegExp_55.RegExp
    Dim Matches As VBScript_RegExp_55.MatchCollection
    Dim Match As VBScript_RegExp_55.Match
    Dim rCell As Range
    Dim SubMatch As Variant
    Dim lCnt As Long
    Dim aPattern(1 To 8) As String

    Set RegEx = New VBScript_RegExp_55.RegExp
    aPattern(1) = "(1?[0-9](:[0-5][0-9])?)" 'time
    aPattern(2) = "( ?)" 'optional space
    aPattern(3) = "([ap]m)?" 'optional ampm
    aPattern(4) = "( ?)" 'optional space
    aPattern(5) = "([ECMP][DS]T)?" 'optional time zone
    aPattern(6) = "( ?)" 'optional space
    aPattern(7) = "(.+?)" 'event description
    aPattern(8) = "(( for )([1-2]?[0-9](.[0-9]?[0-9])?)( hours?))?" 'optional duration

    RegEx.Pattern = Join(aPattern, vbNullString)
    Debug.Print RegEx.Pattern

    Sheet1.Range("C1").Resize(1000, 100).ClearContents

    For Each rCell In Sheet1.Range("A1").CurrentRegion.Columns(1).Cells
        lCnt = 0
        rCell.Offset(0, 2).Value = RegEx.test(rCell.Text)
        If RegEx.test(rCell.Text) Then
            Set Matches = RegEx.Execute(rCell.Text)

            For Each Match In Matches
                For Each SubMatch In Match.SubMatches
                    lCnt = lCnt + 1
                    rCell.Offset(0, 2 + lCnt).Value = SubMatch
                Next SubMatch
            Next Match
        End If
    Next rCell

End Sub


(1?[0-9](:[0-5][0-9])?)( ?)([ap]m)?( ?)([ECMP][DS]T)?( ?)(.+?)(( for )([1-2]?[0-9](.[0-9]?[0-9])?)( hours?))?

# 1

1        2          3        4      5       6       7
5                   pm              CST             H

"H" " ", , "for", . ,

(1?[0-9](:[0-5][0-9])?)( ?)([ap]m)?( ?)([ECMP][DS]T)?( ?)(.+?)( for )([1-2]?[0-9](.[0-9]?[0-9])?)( hours?)

# 7- # 10 , . # 1 , ,

1     2     3     4     5     6     7             8     9     10     11
5           pm          CST         Happy Hour     for  1            hour

, , VBScript , . , , , . , , , kludgy.

?

+3
1

, - . . , , . :

Group 1        Time
Group 2        am/pm
Group 3        Time Zone
Group 4        Description
Group 5        Hours (and fractions of hours)

A2: An . , Submatch " ". - , . , , , .

, "for" , lookahead "". \s + \s +; " ". , , , .

"", , .

Option Explicit
'set Reference to Microsoft VBScript Regular Expressions 5.5
Sub ParseAppt()
    Dim R As Range, C As Range
    Dim RE As RegExp, MC As MatchCollection
    Dim I As Long
Set R = Range("a2", Cells(Rows.Count, "A").End(xlUp))
Set RE = New RegExp
With RE
    .Pattern = "((?:1[0-2]|0?[1-9])(?::[0-5]\d)?)\s*([ap]m)?\s*([ECMT][DS]T)?\s*(.*?(?=\s+for\s+|$))(?:\s+for\s+(\d+(?:\.\d+)?)\s*hour)?"
    .IgnoreCase = True
    For Each C In R
        If .Test(C.Text) = True Then
            Set MC = .Execute(C.Text)
            For I = 0 To 4
                C.Offset(0, I + 1) = MC(0).SubMatches(I)
            Next I
        End If
    Next C
End With
End Sub
+2

More articles:


All Articles