Link to home
Start Free TrialLog in
Avatar of Jeremy Gohman
Jeremy GohmanFlag 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.
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rgonzo1971
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

Avatar of Jeremy Gohman

ASKER

Thank you for the fast response and example.  After some initial testing this seemed to work flawlessly.  I appreciate the help.