Solved

Adding more criteria to a .Pattern code in RegEx

Posted on 2011-03-15
12
539 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 80

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 74

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 80

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 80

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

743 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

13 Experts available now in Live!

Get 1:1 Help Now