Solved

Adding exceptions to the pattern definitions in a RegEx code

Posted on 2011-03-23
11
479 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
  • 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

821 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