Avatar of Jeremy Gohman
Jeremy Gohman
Flag for United States of America asked on

Excel 2016 Data Validation

Hello this may be a long shot but I need some assistance with data validation.

We are currently using a new tool to automate firewall requests in our network which will pull the requested information from an spreadsheet

Currently for the protocol / ports the users are requesting it is formatted like this

Single Port Request:
Users may continue to add as many ports as they require as long as they are separated by a comma

TCP 80, 443

UDP 50

Port Range:
 Users may continue to add as many ports ranges as they require as long as they are separated by a comma

TCP 5000-5010

UDP 5000-5010, 5030-5040

 

The validation that I require is as follows

 

1.) All requests must start with either TCP or UDP not both

2.) All ports must be between 0 to 65535.

3.) No other characters or unnecessary spaces within the cell

 

Any help or insight would be greatly appreciated as I don't even know if this is possible to perform.
Microsoft Excel* Data ValidationMicrosoft Office

Avatar of undefined
Last Comment
Jeremy Gohman

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rgonzo1971

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Rgonzo1971

Corrected regex
Function test_cell(strEMail As String) As Boolean
Dim regex As Object

Set regex = CreateObject("VBScript.RegExp")

With regex
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = "^(TCP|UDP) (([1-9])|([1-9]\d)|([1-9]\d\d)|([1-9]\d{3})|([1-5]\d{4})|(6[0-4]\d{3})|(65[0-4]\d\d)|(655[0-2]\d)|(6553[0-5]))(\-(([1-9])|([1-9]\d)|([1-9]\d\d)|([1-9]\d{3})|([1-5]\d{4})|(6[0-4]\d{3})|(65[0-4]\d\d)|(655[0-2]\d)|(6553[0-5])))*\b" & _
            "(, (([1-9])|([1-9]\d)|([1-9]\d\d)|([1-9]\d{3})|([1-5]\d{4})|(6[0-4]\d{3})|(65[0-4]\d\d)|(655[0-2]\d)|(6553[0-5]))(\-(([1-9])|([1-9]\d)|([1-9]\d\d)|([1-9]\d{3})|([1-5]\d{4})|(6[0-4]\d{3})|(65[0-4]\d\d)|(655[0-2]\d)|(6553[0-5])))*\b)*$"
End With
test_cell = regex.Test(strEMail)

Set regex = Nothing
End Function

Open in new window

Jeremy Gohman

ASKER
Thank you for the fast response and example.  After some initial testing this seemed to work flawlessly.  I appreciate the help.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23