Solved

Adding exceptions to the pattern definitions in a RegEx code

Posted on 2011-03-23
11
478 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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 …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

914 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

22 Experts available now in Live!

Get 1:1 Help Now