Re: Subnetting Validation

Hi,

I'm not quite sure what your level of validation is here. For example, your current formula does nothing to disallow extra digits within the string: both

**192.168.1.1.999**

and

**192.0168.1.1**

to give just two examples, would both be admitted by your current validation formula.

As to your question, an abbreviated version of your current formula would be.

=SUMPRODUCT(N(LOG(1+MID(SUBSTITUTE(B1,".",REPT(" ",10)),{1,11,21,31},10),2)<=8))

Is there a reason we can't just add a further condition to the above (or to your current formula) to check that the last two digits are >=0 and <=32?

Regards

Re: Subnetting Validation

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!

Re: Subnetting Validation

Ok, if you're happy with a minimal level of validation as discussed:

=SUMPRODUCT(N(LOG(1+MID(SUBSTITUTE(SUBSTITUTE(B1,"/","."),".",REPT(" ",10)),{1,11,21,31,41},10),2)<={8,8,8,8,5}))=5

Regards

Re: Subnetting Validation

We can try this also for A2

=AND(ISNUMBER(FIND("/",A2)),SUMPRODUCT(N(LOG(1+MID(SUBSTITUTE(SUBSTITUTE(A2,"/","."),".",REPT(" ",10)),{1,11,21,31,41},10),2)<={8,8,8,8,5}))=5)