Data Validation for IP address in excel

2
votes

I am trying to add data validation for ip address in my cell. The Ip address should be from 0.0.0.0 to 256.256.256.256. To check valid Ip address i am trying to check following conditions:

1.there should be only three dots
2.length of digits should be from 4 to 12.
3.not any digit should be more than 256 or less than 0.
4.it should not take any blanks in between

I am trying through data>data validation>custom>formula>

1.=AND((LEN(C8)-LEN(SUBSTITUTE(C8,".","")))=3,ISNUMBER(SUBSTITUTE(C8,".","")+0))
2.=AND(LEN(C8)-LEN(SUBSTITUTE(C8,".",""))=3,--LEFT(C8,FIND(".",C8)-1)<224,--LEFT(C8,FIND(".",C8)-1)>0,--MID(SUBSTITUTE(C8,"."," "),6,5)<256,--MID(SUBSTITUTE(C8,"."," "),15,7)<256,--MID(SUBSTITUTE(C8,"."," "),22,10)<256)

But my all conditions are not getting satisfied from it.

Please let me know how to add data validation for IP through data validation or conditional formatting.

1
excelvalidationexcel-formulaconditional-formatting
What version of Excel? MAC or Windows? - Ron Rosenfeld
windows @RonRosenfeld - sonali
And the version? (eg 2016, O365, 2007) - Ron Rosenfeld

1 Answers

2
votes

As a custom validation rule, try:

=AND(COUNT(FILTERXML("<t><s>"&SUBSTITUTE(A1,".","</s><s>")&"</s></t>","//s[.*1>-1][.*1<256]"))=4,LEN(A1)-LEN(SUBSTITUTE(A1,".",""))=3)

Where we use FILTERXML to split the string on dots and through XPATH we return those numeric elements ranging from 0-255. Then COUNT will check if there are just 4 elements returned.

The AND is there because we also need to ensure that there are only three dots.

Please do note that using FILTERXML requires Excel 2013 or higher (Excluding Excel Online or Mac).