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.
Jeremy GohmanNetwork EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try this UDF
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


to use the UDF in a validation
Select A1, then in Name Manager define a name called ValidCell whose refersto is:

=test_cell(A1)
(Note: no $ signs in the cell reference)

Then in your data validation use =ValidCell and uncheck the 'Ignore blank' option.

REgards
EE20171101.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rgonzo1971Commented:
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

0
Jeremy GohmanNetwork EngineerAuthor Commented:
Thank you for the fast response and example.  After some initial testing this seemed to work flawlessly.  I appreciate the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.