?
Solved

Adding more criteria to a .Pattern code in RegEx

Posted on 2011-03-15
12
Medium Priority
?
546 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 600 total points
ID: 35143090
       .Pattern = "ROW(S)? (\d+) ?[,-] ?(\d+)"
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 93

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 1400 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
 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

764 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