Solved

Adding exceptions to the pattern definitions in a RegEx code

Posted on 2011-03-23
11
482 Views
Last Modified: 2012-05-11
I have this great code that you guys have been "helping" me put together (yes, I have actually made a few successful tweaks to it!), and now I need to add some exceptions to the pattern definitions.

Occasionally the targeted free text will have a date like "03-March-2011" or "3 July 2010" and it will produce "03-Ma" or "3 J" in the calling cell. I would like to add something that prevents that. I have a named range "months" which contains all the months abbreviated and spelled in full.

Can I add something to the pattern definition that says in effect: "If the text has a number which otherwise satsfies the pattern criteria BUT is followed by one of the strings in 'months', then don't include it."

Thanks,
John
Function GetSeats(InputCell As Range) As String  
    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+)"  
        .Pattern = "(?:ROW|SEAT)(S)?\s*(\d+)\s*([,-/|]|to|thru|through)\s*(\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(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]+"
        .Pattern = "\d+[ |/|-]?[A-M]+" 'Allow up to one space
        '.Pattern = "\d+ *[A-M]+" 'Allow any number of spaces 
        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
  • 7
  • 4
11 Comments
 
LVL 35

Accepted Solution

by:
Terry Woods earned 500 total points
ID: 35203994
Which regex does this apply to?

This one?
        .Pattern = "\d+[ |/|-]?[A-M]+" 'Allow up to one space

You can use a negative lookahead like this:
        .Pattern = "\d+[ |/|-]?(?!jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)[A-M]+" 'Allow up to one space
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 35211363
Boy, you really know this stuff, thanks! Now Iam I right that you can't use a range reference to define your exclusions? The reason I ask is that the list might grow significantly over time and it would be nice to have something more compact if we end up with 40 or 50 exclusions. If a range refernce is possible please let me know.

Thanks Terry,

John

0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 35211458
Terry, one more thing, if you're still there: How do I limit the number matches to 3 or fewer digits?
0
Independent Software Vendors: 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 35

Expert Comment

by:Terry Woods
ID: 35211468
You can't just say (?!jan-dec), but there are ways to reduce the length of patterns, like this:

.Pattern = "\d+[ |/|-]?(?!jan|feb|apr|ma[ry]|ju[ln]|aug|sep|oct|nov|dec)[A-M]+"

What kind of other exclusions might you want? If you can provide examples I might be able to suggest a suitable pattern.

0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 35211470
And thanks for the points, by the way!
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 35211472
at least 1 but no more than 3 digits:

.Pattern = "\d{1,3}[ |/|-]?(?!jan|feb|apr|ma[ry]|ju[ln]|aug|sep|oct|nov|dec)[A-M]+"
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 35211488
By the way, this:
[ |/|-]

Should probably be:
[ /-]
because you don't need | characters to indicate a logical OR within [] brackets. | is only used as a logical OR with round brackets () or without brackets at all eg
match this and (this|or this)
match this|or this
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 35211499
Note also that with pattern:
"\d{1,3}[ /-]?(?!jan|feb|apr|ma[ry]|ju[ln]|aug|sep|oct|nov|dec)[A-M]+"
the following case will match:
1234-J
because the pattern matches 234-J

If you want to avoid that, use something like:
"^\d{1,3}[ |/|-]?(?!jan|feb|apr|ma[ry]|ju[ln]|aug|sep|oct|nov|dec)[A-M]+" '^ matches the start of the string
or
"(?<=\D)\d{1,3}[ |/|-]?(?!jan|feb|apr|ma[ry]|ju[ln]|aug|sep|oct|nov|dec)[A-M]+" 'requires a non-digit before the digits.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 35211515
One more note!:

There is an active "Regular Expressions" zone in EE in case you post any further questions relating to that topic.
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 35211952
Thanks Terry. I have one more question but I decided to post a new one so you can get more points :-)

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26910329.html
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 35211954
Your last suggestion looked like it might do that, but it bugged out for me.
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
need count numbers IN ranges 25 30
Cannot locate cell 15 41
Excel + CountIfs + two colums 5 37
Embed Excel WS into PPTX 4 26
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

734 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