☰

如何使用 Excel 数据验证来验证美国邮政编码？

tag-icon • tag-icon microsoft-excel worksheet-function
如何使用 Excel 数据验证来验证美国邮政编码？

我正在尝试使用 Excel 数据验证来限制美国邮政编码字段的数据输入。我想确认此公式是否完整，或者想找人发布一个更好的公式。

数据验证限制

以下是我想到的一些限制：

  • 10 位数字字符串，其中 5 位数字后接破折号，然后是 4 位数字
  • 5 位数字
  • 包含前导零的 5 位数字字符串（但请参阅下文了解允许的最低数字）
  • 任何邮政编码的最低前 5 位数字是 00501
  • 对于邮政编码 00501，文本“00501”或数字 501 均可以接受。

验证公式

这是我现在使用的验证公式。我正在寻找确认它是否完整或更好的验证公式。

=IF( ISNUMBER(1*$AD$36), AND( 1*$AD$36>=501, 1*$AD$36<=99999), AND( LEN($AD$36)=10, ISNUMBER(1*LEFT($AD$36,5)), 1*LEFT($AD$36,5)>=501, 1*LEFT($AD$36,5)<=9999, ISNUMBER(1*RIGHT($AD$36,4)), MID($AD$36,6,1)="-"))

公式解释

  • 所有实例*1都是将保存为文本的数字转换为实际数字。

  • 如果单元格值是数字（例如，501 或 12345），则检查以确保它是 501 到 99999 之间的有效数字（较长的邮政编码必须包含破折号，使其成为非数字）。

  • 如果单元格值不是数字，则检查字符串的长度是否为 10，前 5 个字符是数字，第 6 个字符是破折号，最后 4 个字符是数字。

答案1

我建议对这些单元格使用自定义格式，以避免处理数字和文本，这将大大简化您的工作。

如果对这些单元格使用自定义格式并且仅输入数字，会怎么样？

例如，使用以下自定义格式（右键单击 -> 设置单元格格式 -> [tab]Number -> 自定义）：

[<=99999]00000;[>99999]00000-0000

然后，检查每个条目有效性的公式将如下所示：

# Absolute reference to the cell ($x$x) may not be necessary but I kept it...

=AND(ISNUMBER($AD$36),IF(LEN($AD$36)>5,INT(LEFT($AD$36,LEN($AD$36)-4)),$AD$36)>=501,$AD$36<=999999999)

旧支票：=AND(ISNUMBER($AD$36),$AD$36>=501,$AD$36<=999999999)

它看起来会是这样的：

未格式化 格式化 验证 信息
400 00400 错误的 太小
501 00501 真的
10008 10008 真的
99999ABCD 99999ABCD 错误的 包含字符
100009 00010-0009 错误的 第一位数字太小
999999 00099-9999 错误的 第一位数字太小
550000000 55000-0000 真的
1234512345 123451-2345 错误的 太长

相关内容