asked on

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

dlmille

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

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dlmille

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

John Carney

ASKER

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

John Carney

ASKER

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

- John