Adding conditions to a REgEx pattern

I have some lines of code based on Regular Expressions, and I would like to add some conditions.

1) I would like to test for the string SEAT(S) as well as ROW(S) here:
          .Pattern = "ROW(S)?\s*(\d+)\s*([,-/|]|to|thru|through)\s*(\d+)"
    I tried combining them with parentheses and a pipe, but that doesn't work.

2) I would like to add something here that allows for a space between the number and the letters
          .Pattern = "\d+[A-M]+"

Below is the entire code.

Thanks,
John
Function GetSeats(InputCell As Range) As String   ' 'brettdj & Brad
    Dim RegEx, RegM, RegMC
    Dim MyDic
    Dim tmpStr As String
    Dim i As Long
 
    Set MyDic = CreateObject("scripting.dictionary")
    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
        '.Pattern = "ROW(S)? (\d+)-(\d+)"
        '.Pattern = "ROW(S)?\s*(\d+)\s*[,-/]\s*?(\d+)" 'Brad
        .Pattern = "ROW(S)?\s*(\d+)\s*([,-/|]|to|thru|through)\s*(\d+)"  'expanded Brad
        .Global = True
        .IgnoreCase = True
        If .test(InputCell.Value) Then
            Set RegMC = .Execute(InputCell)
            For Each RegM In RegMC
                If MyDic.exists(LCase$(RegM)) = False Then
                    For i = RegM.submatches(1) To RegM.submatches(3)
                        Select Case i
                        Case Is <= 4
                            tmpStr = tmpStr & i & "ACDF, "
                        Case Is < 7 > 4
                            tmpStr = tmpStr & i & "ABCDEF, "
                        Case Else
                            tmpStr = tmpStr & i & "ABCDEFHJK, "
                        End Select
                    Next
                    tmpStr = Left$(tmpStr, Len(tmpStr) - 2)
                    MyDic.Add LCase$(RegM), 1
                End If
            Next
        End If
        .Pattern = "\d+[A-M]+"
        If .test(InputCell.Value) Then
            Set RegMC = .Execute(InputCell)
            For Each RegM In RegMC
                If tmpStr = vbNullString Then
                    tmpStr = RegM
                Else
                    tmpStr = tmpStr & ", " & RegM
                End If
            Next
        End If
        GetSeats = tmpStr
    End With
End Function

Open in new window

LVL 1
gabrielPennybackReliability Business Tools Analyst IIAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Terry WoodsConnect With a Mentor IT GuruCommented:
1) This should work:
          .Pattern = "(?:ROW|SEAT)(S)?\s*(\d+)\s*([,-/|]|to|thru|through)\s*(\d+)"

2)
          .Pattern = "\d+ ?[A-M]+" 'Allow up to one space
          .Pattern = "\d+ *[A-M]+" 'Allow any number of spaces
0
 
byundtCommented:
I'm not understanding why TerryAtOpus put the ?: at the front of the pattern. From my testing, you'd prefer it like this:
.Pattern = "(ROW|SEAT)(S)?\s*(\d+)\s*([,-/|]|to|thru|through)\s*(\d+)"
0
 
Terry WoodsIT GuruCommented:
?: makes it a non-capturing group. The asker would need to alter the group reference numbers further down in the code if they added an extra capturing group.
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
byundtConnect With a Mentor Commented:
Note that with the modified pattern, you have an extra parenthetical group. You therefore need to change statement 19 to:
For i = RegM.submatches(2) To RegM.submatches(4)

Open in new window

Brad
0
 
Terry WoodsIT GuruCommented:
Exactly...
0
 
byundtCommented:
TerryAtOpus,
Thanks!

Your syntax wasn't in the web page I use as reference for RegExp syntax, but I see your usage here: http://msdn.microsoft.com/en-us/library/bs2twtah%28v=vs.71%29.aspx

Brad
0
 
gabrielPennybackReliability Business Tools Analyst IIAuthor Commented:
I don't know where I'd be without you people. Yes I do ... I'd be in the unemployment line! :-)

Thanks,
John
0
All Courses

From novice to tech pro — start learning today.