Adding more criteria to a .Pattern code in RegEx

With RegEx
        .Pattern = "ROW(S)? (\d+)-(\d+)"

How do I write this code so that it also picks up on instances where there is:

1) a dash with leading and/or trailing spaces   (as in: " -"  OR   "- "   OR   " - ")
2) a comma with leading and/or trailing spaces   (as in: " ,"  OR   ", "   OR   " , ")

Can all six criteria be added in the one line?

Thanks,
John

With RegEx
        .Pattern = "ROW(S)? (\d+)-(\d+)"
_______________________________________________________

ENTIRE CODE:
Function GetSeats(InputCell As Range) As String   ' 'brettdj
    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+)"
        .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(2)
                        Select Case i
                        Case Is <= 4
                            tmpStr = tmpStr & i & "ACDF, "
                        Case Else
                            tmpStr = tmpStr & i & "ABCDEF, "
                        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
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
John,
Might there be more than one space? If so, you might consider using:
.Pattern = "ROW(S)?\s*(\d+)\s*[,-]\s*?(\d+)"

This pattern will accept:
ROW23-25
ROWS   23  ,   25
ROWS 23-25
ROWS 23,   25

Brad
0
 
zorvek (Kevin Jones)ConsultantCommented:
Try this:

ROW(S)? (\d+)( )?-( )?(\d+)

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Adding test for a comma or a period:

ROW(S)? (\d+)( )?[,-]( )?(\d+)

Kevin
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Terry WoodsConnect With a Mentor IT GuruCommented:
       .Pattern = "ROW(S)? (\d+) ?[,-] ?(\d+)"
0
 
Patrick MatthewsCommented:
John,

Be careful about using the parentheses for the spaces, though, as in Kevin's pattern: if you use the parentheses, you will have to update the later portions of your code where you refer to the submatches.  Thus,

                    For i = RegM.submatches(1) To RegM.submatches(2)

Open in new window


becomes:

                    For i = RegM.submatches(1) To RegM.submatches(4)

Open in new window


TerryAtOpus's suggestion does not introduce any new submatches.

Patrick
0
 
käµfm³d 👽Commented:
>>   if you use the parentheses, you will have to update the later portions

Which is why it's always a good idea to use non-capturing parens when you aren't concerned with capturing a value and your language supports it, which VB/VBA does  : )


Non-capturing version (ellipses denoting some expression):
(?:  ...  )

Open in new window

0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:

Wow!  I appreciate all the answers but Brad's covers the most bases. Kevin, I couldn't get either of yours to work even when I adjusted the number of submatches as per Patrick's suggestion. So forgive me if it's just my ineptitude.

Thanks,

John
0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Brad, I notice that I can test for a forward slash as in: .Pattern = "ROW(S)?\s*(\d+)\s*[,-/]\s*?(\d+)" I'm wondering where can i find a list of all the allowed characters ($ and = don't work).  Also if I'm not pushing my luck, is it possible to test for the words "to" and "thru"? How would that look? I won't be offended if you suggest that I ask a new question on it!

Thanks again,
John
0
 
byundtCommented:
John,
The best way of playing with regular expressions is to use an expression tester together with a syntax guide.
Expression tester http://derekslager.com/blog/posts/2007/09/a-better-dotnet-regular-expression-tester.ashx       Check IgnoreCase and don't use double quotes when pasting the pattern. You can test more than one test string at the same time (put each on a separate line).
Syntax guide http://www.vergelli.it/forum/topic.asp?ARCHIVE=true&TOPIC_ID=20         Here you can see all the special characters explained

To capture the words to and thru, I needed to put all the choices inside parentheses. Using the pattern:
.Pattern = "ROW(S)?\s*(\d+)\s*([,-/]|to|thru)\s*(\d+)"
I found matches for the following:                       Note that you need submatches 1 and 3 to get the row numbers    
ROW23-25
ROWS   23  ,   25
ROWS 23-25
ROWS 23,   25
ROWS 23/27
ROWS 23 to 27
ROWS 23 THRU 27

Brad
0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Amazing, Brad, thanks. i'm really pushing my luck now, but I have two more "quick" questions. I don't want to be taking advantage, but sometimes it's hard to present this sort of thing intelligently in the title of a new question. 1) am I correct  that "\s*' means any number of spaces?
2) Why does this code need "For i = RegM.submatches(1) To RegM.submatches(3)"? Why 3? (4) doesn't work, why not? What would one add to the line of code to warrant (4)?

Regardless, thank you so much for teaching me so much already!

- John

0
 
byundtCommented:
John,
The submatches (each of the bits of the RegEx string inside the parentheses) are numbered 0, 1, 2 and 3. This is because VBA convention is to start numbering collections with subscript 0 (rather than 1).

The first parenthetical expression is the optional "S" after ROW. The second is the first row number. The third is the comma, hyphen, slash, "to" or "thru". The fourth is the second row number.

You need to write statement 22 as:
For i = RegM.submatches(1) To RegM.submatches(3)

Open in new window

because you want the second and fourth parenthetical expression (submatch). These are referenced as subscripts 1 and 3. The reason you would never need subscript 4 is because you want all the rows between 24 and 27--where the 24 is RegM.submatches(1) and the 27 is RegM.submatches(3). Right now, RegM.submatches(4) returns an error because there are only 4 items in the collection--and you would need at least 5 to refer to subscript 4. Now if you added more parenthetical expressions (because you want to tolerate data entry by Bart Simpson), then you might need subscripts 2 and 5. But that's a horse of another color.

\s     'refers to a white space character (such as a regular space)
*      'means 0 or more
\s*   'means 0 or more white space characters

Brad
0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Wow again, Brad, thanks. As usual you've taught me a great deal. Just enough, I think, to be able to understand any online tutorials.

RE: Bart Simpson: Bart is far more precise and conscientious than some of the people who provide the data entry! :-)

Hope all is well.

- John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.