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

我正在尝试浏览数据>数据验证>自定义>公式>

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需要Excel 2013或更高版本(不包括Excel Online或Mac(。