excel - 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)。