☰

Excel 数据验证自定义公式：如何定义具有定义字符和数字位置的字母数字格式

tag-icon • tag-icon microsoft-excel
Excel 数据验证自定义公式：如何定义具有定义字符和数字位置的字母数字格式

我正在尝试将数据验证应用于特定单元格的输入。结构需要格式化为@@##-######，其中 @ = 字符串和 # = 数字。我尝试了各种自定义公式，但由于某种原因，它们不起作用。我的第一次尝试是定义为

=AND(ISTEXT(LEFT(C11,2)),ISNUMBER(AND(MID(C11,3,2),RIGHT(C11,6))))

但是，我不确定为什么这不起作用，我猜是因为字符串的剩余数字被视为文本而不是数字？

接下来，我尝试修改在线找到的类似用例的现有示例，只是它们的结构不同，所以我尝试对其进行相应的调整......

=AND(ISTEXT(LEFT(C11,2)),SUMPRODUCT(--ISNUMBER(--AND(MID(C11,3,2),RIGHT(C11,6)))=8))

...但它仍然不起作用。我一开始还没有完全理解它是如何工作的，所以我无法分辨哪些有效，哪些无效，以及原因。

任何援助将不胜感激！

附言：我意识到这些没有考虑连字符，我想我会在先弄清楚字符串和数字之后再解决这个问题。

答案1

我认为问题在于 LEFT 和 MID 函数始终返回字符串，即使从技术上讲它们可以包含数字，ISNUMBER 函数仍将返回 false，因为它将它们视为字符串。同样，出于同样的原因，ISTEXT 函数在与 LEFT 或 MID 一起使用时将始终返回 true。除了 ISTEXT，您还可以使用 NOT、ISERROR 和 NUMBERVALUE 的某种组合，如下所示：

检查第一个字符是否是字符串：

=ISERROR(NUMBERVALUE(LEFT(A1,1)))

检查第二个字符是否是字符串

=ISERROR(NUMBERVALUE(MID(A1,2,1)))

检查位置 3 和 4 是否为数字

=NOT(ISERROR(NUMBERVALUE(MID(A1,3,2))))

检查位置 5 是否为连字符

=MID(A1,5,1)="-"

检查最右边 6 个位置是否为数字

=NOT(ISERROR(NUMBERVALUE(RIGHT(A1,6))))

检查总长度是否为 11

=LEN(A1)=11

以下是所有这些通过 AND 连接在一起的结果：

=AND(ISERROR(NUMBERVALUE(LEFT(A1,1))),ISERROR(NUMBERVALUE(MID(A1,2,1))),NOT(ISERROR(NUMBERVALUE(MID(A1,3,2)))),MID(A1,5,1)="-",NOT(ISERROR(NUMBERVALUE(RIGHT(A1,6)))),LEN(A1)=11)

或者，解决方案 2：

如果您在工作簿中进行大量文本验证，您可以考虑使用正则表达式。但据我所知，您必须使用 VBA 来启用它。插入新模块并将此功能放入：

Function matchesRegex(stringToBeMatched As String, rgx As String, ByVal ignoreCase As Boolean) As Boolean
    If rgx = vbNullString Then
        matchesRegex = True
        Exit Function
    End If
    Dim regEx As New RegExp
    With regEx
        .Global = True
        .MultiLine = True
        .ignoreCase = ignoreCase
        .Pattern = rgx
    End With
    matchesRegex = regEx.test(stringToBeMatched)
End Function

然后您必须在工具->参考中启用此参考：

在此处输入图片描述

现在您可以在工作簿中使用 matchesRegex 函数，如下所示：

=matchesRegex(A1,"\D{2}\d{2}-\d{6}",TRUE)

这个小函数的作用与上面所有内置函数完全相同。因此，如果您经常做这些事情，我建议您学习正则表达式并使用它们。

相关内容