Subnetting Validation

Hello -

I found a formula that will validate if the IP address that I enter in a cell is correct:

Please Login or Register  to view this content.

However, I this validation is only for the ip address (for example, 192.168.1.1).

I need to validate the user is entering the correct IP with subnet information (for example, 192.168.1.1/24).

Is there a way to modify this validation to look for the subnet cdr at the end of the IP? (/32, /31, /30..... /1).

Thanks,

Scott

Thanks for your response!

Looks like you are correct. My long validation does not really work properly.

I like your simplified version. But not really sure I understand it. Plus it seems to output a value instead of true or false (192.168.1.1 = 4).

I should have put more detail in my original post.

My goal is to validate IP subnet so the address is somewhere between 0.0.0.0 and 255.255.255.255 and the cider at the end of the block is between /1 and /32.

For example:
192.168.1.1/24 = TRUE
100.64.0.0/16 = TRUE
1000.10.10.0/24 = FALSE
10.10.10.0/45 = FALSE

(I know the formula will not catch incorrect ip blocks... But this should catch little mistakes and makes sure the user enters the data in the field properly.)

Seems like there would be an easy way to do this... Just not sure what it would be. Thanks again for looking at this for me! Really appreciate your help!