使用正则表达式（REGEX）的Excel数据验证-excel-PHP中文网

在限制Excel工作表中的用户输入时，数据验证是必不可少的。想要在给定的单元格中仅允许数字或日期吗？还是将文本值限制为特定的长度？还是禁止在给定范围之外的时间？没问题，所有这些都可以通过预设或自定义验证标准轻松完成。但是，如果我只想允许匹配特定模式的有效电子邮件地址或字符串怎么办？ las，这是不可能的。你说的正则是吗？嗯...可能会起作用！

如何使用REGEX进行Excel数据验证

遗憾的是，内置的Excel都没有支持Regexes，并且数据验证也不例外。为了能够使用正则表达式验证单元格输入，您需要先创建一个自定义的正则函数。另一个复杂的是，VBA用户定义的功能不能直接将数据验证提供给数据验证 - 您需要以命名公式的形式进行调解员。

考虑到上述内容，让我们简要概述使用Regexes中验证Excel中的数据的步骤：

创建一个自定义的正则函数，该功能检查输入值是否匹配正则表达式。
为您的正则公式定义名称。
根据命名公式配置数据验证规则。
将验证设置复制到所需的尽可能多的单元格。

听起来像计划吗？让我们尝试在实践中实施它！

使用自定义表达式的Excel数据验证

此示例解决了一个非常常见的情况 - 如何仅允许特定模式的值。

假设您在工作表中保留一些SKU代码，并希望确保只有与给定模式匹配的代码进入列表。前提是每个SKU由两组与连字符分开的字符组成，其中的第一组包括3个大写字母和第二个组-3个数字，您可以使用以下正则表达式识别此类值。

模式： ^[az] {3} - \ d {3} $

请注意，字符串的开始（^）和末端（$）是锚定的，因此除了模式以外的字符都无法输入单元格中。

1。添加自定义的正则匹配功能

首先在工作簿中插入RegexPMatch功能。该代码已经由我们的Excel Gurus编写，因此您只需要从上述页面复制它并在VBA编辑器中粘贴即可。

这是函数的语法供您参考：

regexpMatch（文本，模式，[match_case]）

在哪里：

文本（必需） - 源字符串（在我们的上下文中 - 经过验证的单元格）。
模式（必需） - 匹配的正则表达式。
match_case （可选） - 匹配类型。真实或省略 - 对病例敏感；错误 - 不敏感的情况。

提示。如果您是我们的Ultimate Suite的用户，则可以在Excel中进行正则数据验证，而无需在工作簿中添加任何VBA代码。只需利用我们的Regex工具中包含的自定义Ablebitsregexmatch功能即可。

2。创建一个命名公式

在您的目标工作表中，选择单元格A1（无论您实际要验证哪个单元格，无论其内容如何），按CTRL F3打开名称管理器，并为此公式定义名称：

=RegExpMatch(Sheet1!A1, "^[AZ]{3}-\d{3}$")

或者，您可以在某些单元格中输入正则罚款（在此示例中的A2），并向第二个参数提供$ 2：

=RegExpMatch(Sheet1!A1, Sheet1!$A$2)

为了使公式正确工作，请确保为文本参数（A1）和模式的绝对参考使用相对参考（$ a $ 2）。

鉴于我们的公式目的是验证SKU号码，因此我们将其命名： Validate_sku 。

使用正则表达式（REGEX）的Excel数据验证

重要说明！定义公式时，请仔细检查第一个参数是否指当前选择的单元格，否则公式将无法使用。例如，如果在表上选择了单元格A1，请将A1放入第一个参数（根据我们的建议）；如果选择了B2，则将B2用于第一个参数，也是如此。只要与当前选择的单元格匹配，您使用哪个特定参考文献并不重要。

有关分步说明，请查看如何在Excel中制作命名公式。

3。设置数据验证

选择要检查要检查的第一个单元格（在我们的情况下为A5），并根据命名公式制定自定义数据验证规则。为此，请执行以下操作：

单击数据选项卡>数据验证。
在“允许”下拉列表中，选择自定义。
在相应的框中输入以下公式。
=Validate_SKU
取消选择忽略空白选项，否则您的规则将无法正常工作。

使用正则表达式（REGEX）的Excel数据验证

可选地，您可以输入在单元格中输入无效数据时显示的自定义错误消息。

使用正则表达式（REGEX）的Excel数据验证

如果您觉得需要详细的步骤，那么您可以使用：如何在Excel中设置自定义数据验证。

4。将数据验证复制到更多单元

要将验证设置复制到更多的单元格，这就是您需要做的：

选择具有数据验证的单元格，然后按CTRL C复制。
选择要验证的其他单元格，右键单击它们，单击“粘贴”特价，然后选择“验证”选项。
单击确定。

有关如何复制数据验证的更多信息。

现在，每当有人试图在任何经过验证的单元格中输入无效的SKU时，都会出现以下警告消息：

使用正则表达式（REGEX）的Excel数据验证

用正则验证的电子邮件验证

要执行电子邮件验证，您从编写与电子邮件地址匹配的正则表达式开始。

模式： ^[\ w \。\ - ] @[A-ZA-Z0-9] [A-ZA-Z0-9 \。\ - \ - ]*[A-ZA-Z0-9] \。

有关语法的详细说明，请参阅Regex以匹配有效的电子邮件地址。

现在，通过执行已经熟悉的步骤来指定验证标准：

在B2中输入上述正则罚款。
选择单元格A1并定义一个名为validate_email的名称，该名称是指：
=RegExpMatch(Sheet1!A1, Sheet1!$B$2)
对于单元格B5，请使用以下公式应用自定义数据验证。至关重要的是，应忽略空白选项。
=Validate_Email

此外，您可以配置自定义错误消息，以提示用户输入有效的电子邮件地址。
将规则复制到下面的单元格。