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

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
LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
dlmilleCommented:
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
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
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
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks, Dave. I'm still not completely out of the woods, but this will definitely get me started. Stay tuned!

- John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.