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.
Avatar of Rgonzo1971

Link to home
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

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


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