excel中IP地址的数据校验

Question

我正在尝试为我的计算单元中的ip地址添加数据验证。Ip地址应从0.0.0.0到256.256.256.256。为了检查有效的Ip地址，我尝试检查以下条件：

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

我正在尝试通过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)

但是我的所有条件都没有得到满足。

请告诉我如何通过数据验证或条件格式为IP添加数据验证。

Answer 1

作为自定义验证规则，请尝试：

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

在这里，我们使用FILTERXML将字符串拆分成点，然后通过XPATH返回0-255之间的数值元素。然后COUNT将检查是否只返回了4个元素。

AND在那里，因为我们还需要确保只有三个点。

请注意，使用FILTERXML需要Excel2013或更高版本(不包括Excel Online或Mac)。