?
Solved

Tweaking a RegEx function so that it excludes certain patterns that might otherwise be matches, and more.

Posted on 2011-05-10
6
Medium Priority
?
362 Views
Last Modified: 2012-08-13
In the code below, how do I limit my accepted alphanumerics to ones containing no more than 3 digits, and how do I exclude cells - that would otherwise match the pattern - if certain strings are also present?
I thought that this line would accomplish both of these things but it doesn't:

.Pattern = "(\d+)[ /-]?(?!jan|feb|apr|ma[ry]|ju[ln]|aug|sep|oct|nov|dec|in|by|mi|hrs|check|GMT|jump|and)[A-M]+"

Finally, how would I combine the two patterns below into one. Or at least position them within the same IF statement. Assuming that's possible.

I've attached a workbook if that helps.

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|SEAT)(S)?\s*(\d+)\s*([,-/|]|to|thru|through)\s*(\d+)"
        .Global = False
        .IgnoreCase = True
        If .test(InputCell.Value) Then
            Set RegMC = .Execute(InputCell)
            For Each RegM In RegMC
                    tmpStr = "PAX"
            Next
        End If
'How do I combine the pattern above with this one?
        .Pattern = "(\d+)[ /-]?(?!jan|feb|apr|ma[ry]|ju[ln]|aug|sep|oct|nov|dec|in|by|mi|hrs|check|GMT|jump|and)[A-M]+"
        .Global = False
        .IgnoreCase = True
        If .test(InputCell.Value) Then
            Set RegMC = .Execute(InputCell)
            For Each RegM In RegMC
                    tmpStr = "PAX"
            Next
        End If
        getSeats = tmpStr
    End With
End Function

Open in new window

GetSeatCodes.xls
0
Comment
Question by:gabrielPennyback
  • 4
  • 2
6 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 35731398
I need to put my RegEx hat back on for this one, and will try - but other E-E experts will undoubtedly beat me to it (given my priorities for the day).  However, a quick simplification of your code I can provide based on "50,000 foot" observation:

 
Function GetSeats(InputCell As Range) As String   ' 'brettdj & Brad
    Dim RegEx, RegM, RegMC
    Dim MyDic
    Dim tmpStr As String
    Dim i As Long
    Dim test1 As Boolean, test2 As Boolean
    
    Set MyDic = CreateObject("scripting.dictionary")
    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
        .Global = False
        .IgnoreCase = True
        
        .Pattern = "(?:ROW|SEAT)(S)?\s*(\d+)\s*([,-/|]|to|thru|through)\s*(\d+)"
        test1 = .test(InputCell.Value)
        
        .Pattern = "\d{1,3}[ /-]?(?!jan|feb|apr|ma[ry]|ju[ln]|aug|sep|oct|nov|dec|in|by|mi|hrs|check|GMT|jump|and)[A-M]+"
        test2 = .test(InputCell.Value)
        
        If test1 Or test2 Then
            Set RegMC = .Execute(InputCell)
            For Each RegM In RegMC
                    tmpStr = "PAX"
            Next
        End If
        GetSeats = tmpStr
    End With
End Function

Open in new window


PS - it looks like you got some help with this initial function - you might go back to that posting and post a link to this one to reel them in :)

Dave
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 35731450
And, here's a straight combine of the two patterns without thinking through merge logic:

 
Function GetSeats(InputCell As Range) As String   ' 'brettdj & Brad
    Dim RegEx, RegM, RegMC
    Dim MyDic
    Dim tmpStr As String
    Dim i As Long
    Dim test1 As Boolean, test2 As Boolean
    Dim myPattern1 As String, myPattern2 As String
    Set MyDic = CreateObject("scripting.dictionary")
    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
        .Global = False
        .IgnoreCase = True
        
        myPattern1 = "(?:ROW|SEAT)(S)?\s*(\d+)\s*([,-/|]|to|thru|through)\s*(\d+)"
        
        myPattern2 = "\d{1,3}[ /-]?(?!jan|feb|apr|ma[ry]|ju[ln]|aug|sep|oct|nov|dec|in|by|mi|hrs|check|GMT|jump|and)[A-M]+"
        
        .Pattern = "(?:" & myPattern1 & "|" & myPattern2 & ")"
        
        If .test(InputCell.Value) Then
            Set RegMC = .Execute(InputCell)
            For Each RegM In RegMC
                    tmpStr = "PAX"
            Next
        End If
        GetSeats = tmpStr
    End With
End Function

Open in new window


Let me know if this works for you!  I'll start giving your new criteria some thought, but again - it may take me a while as I have some other things on my plate.

Any E-E experts who want to pitch in - welcome!

Dave
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 2000 total points
ID: 35731577
PS - I posted with code based on the last question, so myPattern2 you'll want to change back to this question's post, e.g.,:

myPattern2= "(\d+)[ /-]?(?!jan|feb|apr|ma[ry]|ju[ln]|aug|sep|oct|nov|dec|in|by|mi|hrs|check|GMT|jump|and)[A-M]+"


Here's a couple RegExp patterns allowing no more than 3 alphanumeric, at least to give you some food for thought:
[A-Z0-9]{1,3}

or

\w{1,3}

The \d{1,3} you had in the prior question only looked for numbers, instead of alphanumeric


Hope this helps!

Dave
0
Technology Partners: 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 42

Expert Comment

by:dlmille
ID: 35755649
I see you've had several other posts over the last couple days and may not have gotten back to this.  Was what I submitted any help?

Do you still need RegEx help?  I can assist further, now, especially if you can give me a dataset of inputs and what the outputs are supposed to look like.  That way I can do some testing with your input.  Re: its abit hard to take on the challenge of fixing a RegEx by interpreting what it does without knowing what its supposed to do after tweaks, if my proposed approach wasn't satisfactory...

PS - did the combination into one IF statement work fine for you?

Dave
0
 
LVL 1

Author Comment

by:gabrielPennyback
ID: 35774839
Hi Dave, pls forgive me for being AWOL. I'm pretty hopeful that your solutions will solve my problem. I'm going to email myself to check this out firstthing tomorrow morning at work.

Thanks,
John
0
 
LVL 1

Author Closing Comment

by:gabrielPennyback
ID: 36297441
Thanks, Dave. I'm still not completely out of the woods, but this will definitely get me started. Stay tuned!

- John
0

Featured Post

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.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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…

840 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