Solved

Adding more criteria to a .Pattern code in RegEx

Posted on 2011-03-15
12
541 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:gabrielPennyback
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35143066
Try this:

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

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35143078
Adding test for a comma or a period:

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

Kevin
0
 
LVL 35

Assisted Solution

by:Terry Woods
Terry Woods earned 150 total points
ID: 35143090
       .Pattern = "ROW(S)? (\d+) ?[,-] ?(\d+)"
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 35143133
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
 
LVL 81

Accepted Solution

by:
byundt earned 350 total points
ID: 35143211
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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35143212
>>   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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:gabrielPennyback
ID: 35143505

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
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 35143575
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
 
LVL 81

Expert Comment

by:byundt
ID: 35143707
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
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 35143820
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
 
LVL 81

Expert Comment

by:byundt
ID: 35144119
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
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 35151687
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now