?
Solved

Adding exceptions to the pattern definitions in a RegEx code

Posted on 2011-03-23
11
Medium Priority
?
486 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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

762 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