正则提取在Excel中提取字符串（一个或所有匹配）

在本教程中，您将学习如何在Excel中使用正则表达式来查找和提取与给定模式相匹配的子字符串。

Microsoft Excel提供了许多功能，可以从单元格中提取文本。这些功能可以应对工作表中的大多数字符串提取挑战。大多数，但不是全部。当文本功能偶然发现时，正则表达式救援。等等…Excel没有正则功能！是的，没有内置功能。但是没有什么可以阻止您使用自己的:)

• VBA REGEXP提取功能
  • 从字符串提取号码
  • 获取所有比赛
  • 从字符串获取电子邮件地址
  • 提取电子邮件域
  • 提取电话号码
  • 提取日期
  • 选择时间
• .NET REGEX提取功能
  • 在两个字符之间提取文字
  • 在两个字符串之间提取文字
  • 从URL获取域名

Excel VBA正则函数提取字符串

要在Excel中添加自定义的Regex提取功能，请在VBA编辑器中粘贴以下代码。为了在VBA中启用正则表达式，我们正在使用内置的Microsoft Regexp对象。该代码由我们的Excel专家Alex Frolov编写。

public函数reggexpextract （文本为字符串，字符串，可选instance_num as integer = 0 ，可选match_case as boolean = true ）​​​ REGEX.MULTILINE = true如果true = Match_case the Regex.ignorecase = else else regex.ignorecase = true End如果设置匹配= regex.execute = regex.execute（text）如果0 ，则if （0 = instance_num） ，然后redim text_matches（ artees.count.count.count.count.count -1，0，0，0） text_matches(matches_index, 0) = matches.Item(matches_index) Next matches_index RegExpExtract = text_matches Else RegExpExtract = matches.Item(instance_num - 1) End If End If Exit Function ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function

如果您对VBA的经验很少，则分步用户指南可能会有所帮助：如何在Excel中插入VBA代码。

笔记。对于工作功能，请确保将您的文件保存为宏支持的工作簿（.XLSM）。

Regexpextract语法

Regexpextract函数搜索输入字符串以匹配正则表达式并提取一个或所有匹配的值。

该函数具有以下语法：

regexpextract（文本，模式，[instance_num]，[match_case]）

在哪里：

• 文本（必需） - 要搜索的文本字符串。
• 模式（必需） - 匹配的正则表达式。当直接以公式提供时，应以双引号标记封闭该图案。
• instance_num （可选） - 指示要提取的实例的序列号。如果省略，则返回所有找到的匹配项（默认）。
• match_case （可选） - 定义是匹配还是忽略文本案例。如果true或省略（默认），则执行对案例敏感的匹配；如果是错误的 - 不敏感的。

该功能在Excel 365，Excel 2021，Excel 2019，Excel 2016，Excel 2013和Excel 2010的所有版本中都起作用。

关于Regexpextract的4件事

要有效地使用Excel中的功能，需要注意一些重要的事情：

1. 默认情况下，该函数将所有发现的匹配返回到相邻单元格中，如本示例所示。要获取特定的事件，请向instance_num参数提供相应的数字。
2. 默认情况下，该函数对病例敏感。对于对案例不敏感的匹配，将match_case参数设置为false。由于VBA的限制，对案例不敏感的结构（？i）无法正常工作。
3. 如果找不到有效的模式，则该函数将不返回（空字符串）。
4. 如果图案无效，则为#Value！发生错误。

在开始在工作表中使用此自定义功能之前，您需要了解它的能力，对吗？以下示例涵盖了一些常见用例，并解释了为什么动态阵列Excel（Microsoft 365和Excel 2021）和传统Excel（2019及以上版本）的行为可能有所不同。

笔记。罚款示例是为童话简单数据集编写的。我们不能保证它们在您的真实工作表中会完美工作。那些拥有正则经验的人会同意，编写正则表达方式是一条永无止境的完美之路 - 几乎总是有一种方法使其更优雅或能够处理更广泛的输入数据。

从字符串中提取数字的正则

遵循“从简单到复杂”的基本教学最大值，我们将从一个非常简单的情况开始：从字符串中提取数字。

您要决定的第一件事是要检索哪个数字：首先，最后，特定的发生或所有数字。

提取第一个数字

这很简单，就像发行正则可以得到的那样。鉴于\ d表示从0到9的任何数字，而指的是一次或多次，我们的正则表达式采用此形式：

模式：\ d

将instance_num设置为1，您将获得所需的结果：

=RegExpExtract(A5, "\d ", 1)

其中A5是原始字符串。

为了方便起见，您可以将图案输入预定义的单元格（$ a $ 2），并用$标志锁定其地址：

=RegExpExtract(A5, $A$2, 1)

获取最后一个数字

要在字符串中提取最后一个数字，以下是要使用的模式：

模式： （\ d）（？！。*\ d）

它被翻译成人类语言，说：找到一个没有其他数字的数字（不仅在任何地方）。为了表达这一点，我们使用了负面的lookahead（？！。*\ d），这意味着在模式的右边，无论其前面有多少其他字符，都不应该有其他数字（\ d）。

=RegExpExtract(A5, "(\d )(?!.*\d)")

尖端：

• 要获得特定的发生，请使用\ d进行模式，并为实例_num使用适当的序列号。
• 在下一个示例中讨论了提取所有数字的公式。

提取所有匹配的正则

进一步推动我们的示例，假设您想从字符串中获取所有数字，而不仅仅是一个。

您可能还记得，提取的匹配数由可选的instance_num参数控制。默认值是所有匹配项，因此您只需省略此参数：

=RegExpExtract(A2, "\d ")

该公式可用于单个单元格，但是动态阵列Excel和非动态版本的行为有所不同。

Excel 365和Excel 2021

由于支持动态阵列，常规公式会自动溢出到需要显示所有计算结果的尽可能多的单元中。就excel而言，这称为溢出范围：

Excel 2019和较低

在动态前的Excel中，上述公式只会返回一场比赛。要获得多个匹配，您需要使其成为阵列公式。为此，选择一系列单元格，键入公式，然后按Ctrl Shift进入以完成它。

这种方法的缺点是在“额外的单元格”中出现了一堆＃N/A错误。遗憾的是，对此无能为力（iferror和Ifna都无法修复它，可惜）。

在一个单元格中提取所有匹配

处理数据列时，上述方法显然无法正常工作。在这种情况下，理想的解决方案将返回单个单元格中的所有匹配。要完成它，请将RegexPextract的结果提供给文本Join函数，并将它们与您喜欢的任何定界符分开，例如逗号和空间：

=TEXTJOIN(", ", TRUE, RegExpExtract(A5, "\d "))

笔记。由于TextJoin功能仅在Microsoft 365，Excel 2021和Excel 2019的Excel中可用，因此该公式在较旧版本中无法使用。

从字符串中提取文本的正则

在Excel中，从字母数字字符串中提取文本是一项艰巨的任务。有了正则表达式，它变得像馅饼一样容易。只需使用否定的类与所有不是数字的所有内容匹配。

模式：[^\ d]

为了获得单个细胞（溢出范围）的子字符串，公式为：

=RegExpExtract(A5, "[^\d] ")

要将所有匹配输出到一个单元格中，请以这样的文字嵌套regexpextract函数：

=TEXTJOIN("", TRUE, RegExpExtract(A5, "[^\d] "))

从字符串中提取电子邮件地址的正则

要从包含许多不同信息的字符串中提取电子邮件地址，请编写一个重复电子邮件地址结构的正则表达式。

模式：[\ w \。\ - ] @[a-za-z0-9 \。\ - ] \。[a-za-z] {2,24}

打破了这一正则是我们得到的：

• [\ w \。\ - ]是一个用户名，可能包括1个或更多字母数字，下划线，点和连字符。
• @ 象征
• [A-ZA-Z0-9 \。\ - ]是一个域名，由：大写和小写字母，数字，连字符和点（如果是子域）。在这里不允许下划线，因此使用3个不同的字符集（例如AZ AZ和0-9），而不是与任何字母，数字或下划线相匹配的\ w。
• \。[A-ZA-Z] {2,24}是顶级域。由一个点组成，然后是大写和小写字母。大多数顶级域是3个字母长（例如.com .org，.edu等），但从理论上讲，它可以包含2到24个字母（最长的注册tld）。

假设字符串为A5，并且在A2中的模式，则提取电子邮件地址的公式为：

=RegExpExtract(A5, $A$2)

从电子邮件中提取域的正则

在提取电子邮件域时，想到的第一个想法是使用捕获组来查找紧随 @字符的文本。

模式： @（[A-ZA-Z0-9 \。\ - ] \。[A-ZA-Z] {2,24}）

将其提供给我们的REGEXP功能：

=RegExpExtract(A5, "@([A-Za-z0-9\.\-] \.[A-Za-z]{2,24})")

您会得到这个结果：

借助经典的正则表达式，提取中不包括捕获组以外的任何东西。没有人知道为什么VBA Regex的工作方式有所不同，并且也捕获了“@”。为了摆脱它，您可以通过用空字符串替换结果从结果中删除第一个字符。

=REPLACE(RegExpExtract(A5, "@([az\d][az\d\-\.]*\.[az]{2,})", 1, FALSE), 1, 1, "")

提取电话号码的正则表达式

电话号码可以以多种不同的方式编写，这就是为什么在任何情况下都无法提出解决方案的原因。但是，您可以写下数据集中使用的所有格式并尝试匹配它们。

在此示例中，我们将创建一个正则是以任何以下格式提取电话号码的正则号：

（123）345-6789 （123）345 6789 （123）3456789 123-345-6789

123.345.6789 123 345 6789 1233456789

模式：\（？\ d {3} [ - \。\）]*\ d {3} [ - \。 ]？\ d {4} \ b

• 第一部分\（？\ d {3}匹配零或一个开口括号，然后是三位数d {3}。
• 这 [-\。 \）]*零件是指在0或更多次出现的方括号中的任何字符：连字符，周期，空间或闭合括号。
• 接下来，我们再次有三位数字D {3}，然后是任何连字符，周期或空间[ - \。 ]？出现0或1次。
• 之后，有一组四位数\ d {4}。
• 最后，有一个单词边界\ b定义我们正在寻找的电话号码不能成为更大数字的一部分。

完整的公式采用这种形状：

=RegExpExtract(A5, "\(?\d{3}[-\. \)]*\d{3}[-\. ]?\d{4}\b")

请注意，上述正则返回一些虚假阳性结果，例如123）456 7899或（123 456 7899。以下版本解决了这些问题。但是，该语法仅在VBA RegexP函数中起作用，而不是在经典正则表达式中。

模式：（\（\ d {3} \）| \ d {3}）[ - \。 ]？\ d {3} [ - \。 ]？\ d {4} \ b

从字符串提取日期

提取日期的正则表达式取决于日期出现在字符串中的格式。例如：

要提取如1/1/21或01/01/2021之类的日期，REGEX为：\ D {1,2} \/\ d {1,2} \/（\ d {4} | \ d {2}））

它搜索一组1或2位数字d {1,2}，然后是斜线，然后是另一组1或2位数字，然后是斜线，然后是一组4或2位数字（\ d {4} | \ d {2 {2}）。请注意，我们首先要寻找4位数年，直到那时才有2位数年。如果我们以相反的方式编写它，那么多年来只会挑选前2位数字。这是因为在第一个条件在交替或结构匹配之后，未检查其余条件。

要检索1-JAN-21或01-JAN-2021之类的日期，模式为：\ d {1,2} - [A-ZA-Z] {3} {3} - \ d \ d {2,4}

它搜索一组1或2位数字，然后是连字符，然后是一组3个大写字母或小写字母，然后是连字符，然后是4或2位数字的组。

将两种模式组合在一起后，我们得到以下等级：

模式：\ b \ d {1,2} [\/ - ]（\ d {1,2} | [a-za-z] {3}）{\/ - ] [\/ - ]（\ d {4} | \ d {2}）\ b

在哪里：

• 第一部分是1或2位数字：\ d {1,2}
• 第二部分是1或2个字母：（\ d {1,2} | [a-za-z] {3}）{3}）
• 第三部分是4或2位数字的组：（\ d {4} | \ d {2}）
• 定界符是前向斜线或连字符：[\/ - ]
• 单词边界\ b都放在两边，以表明日期是一个单独的单词，而不是较大字符串的一部分。

如下图所示，它成功地拉出了日期，并将其排除在11/22/333等子字上。但是，它仍然返回假阳性结果。在我们的情况下，A9中的substring 11-ABC-2222在技术上与日期格式DD-MMM-yyyy匹配，因此提取。

为了消除误报，您可以用3个字母的缩写的完整列表替换[A-Za-Z] {3}部分：

模式：\ b \ d {1,2} [\/ - ]（\ d {1,2} |（Jan | feb | Mar | Mar | Apr | Apr | Apr | May | Jun | Jun | Jun | Jun | aug | aug | sep | sep | sep | oct | nock | dec））[\/ - ]（\ d {4} | \ d {2}）\ b

为了忽略字母案例，我们将自定义函数的最后一个参数设置为false：

=RegExpExtract(A5, $A$2, 1, FALSE)

这次，我们得到了一个完美的结果：

从字符串中提取时间

要在HH中获得时间：MM或HH：MM：SS格式，以下表达式将有效。

模式：\ b（0？[0-9] | 1 [0-2]）：[0-5] \ d（：[0-5] \ d）？\ s？（am | pm）\ b | \ b（[0-9] | [0-1] \ D | 2 | 2 [0-3] [0-3]）

分解这条正则是|这样可以或逻辑。换句话说，我们搜索与以下表达式之一相匹配的子字符串。

表达式1 ：\ b（0？[0-9] | 1 [0-2]）：[0-5] \ d（：[0-5] \ d）？\ s？（am | pm）\ b

通过AM/PM检索时间。

小时可以是从0到12的任何数字。要获得它，我们使用或构造（[0-9] | 1 [0-2]），其中：

• [0-9]匹配从0到9的任何数字
• 1 [0-2]匹配10到12的任何数字

分钟[0-5] \ d是00到59的任何数字。

第二（：[0-5] \ d）？也有任何数字从00到59。量词是指零或一次发生，因为秒可能会或可能不包含在时间值中。

表达式2 ：\ b（[0-9] | [0-1] \ d | 2 [0-3]）：[0-5] \ d（：[0-5] \ d）？

提取无am/pm的时间。

小时部分可以是从0到32的任何数字。要获得它，使用了不同的或构造（[0-9] | [0-1] \ d | 2 [0-3]），其中：

• [0-9]匹配从0到9的任何数字
• [0-1] \ d匹配00到19的任何数字
• 2 [0-3]匹配20到23的任何数字

分钟和第二部分与上面的表达式1相同。

负lookahead（？！:)添加到跳过弦，例如20:30:80。

由于PM/AM可以是大写或小写，因此我们使功能不敏感：

=RegExpExtract(A5, $A$2, 1, FALSE)

希望以上示例为您提供了一些关于如何在Excel工作表中使用正则表达式的想法。不幸的是，VBA并非支持经典正则表达式的所有功能。如果您的任务无法通过VBA RegexP完成，我鼓励您阅读下一部分讨论功能更强​​大的.NET REGEX功能。

基于.NET的Custom Regex功能以在Excel中提取文本

与任何Excel用户可以编写的VBA REGEXP函数不同，.NET REGEX是开发人员的领域。 Microsoft .NET框架支持与Perl 5兼容的完整的正则表达语法。本文不会教您如何编写此类功能（我不是程序员，并且对如何执行此操作没有丝毫想法。

标准.NET REGEX引擎处理的四个强大功能已经由我们的开发人员编写，并包括在Ultimate Suite中。下面，我们将演示该功能的一些实际用途，该功能专门设计用于在Excel中提取文本。

提示。有关.NET REGEX语法的信息，请参阅.NET正则表达语言。

如何使用正则表达式在Excel中提取字符串

假设您已安装了最新版本的Ultimate Suite，则使用正则表达式提取文本归结为这两个步骤：

1. 在“ ablebits数据”选项卡上，在文本组中，单击Regex工具。

   

2. 在Regex Tools Pane上，选择源数据，输入您的REGEX模式，然后选择“提取”选项。要将结果作为自定义功能而不是值，请选择插入作为公式复选框。完成后，单击“提取”按钮。

   

结果将出现在您原始数据右侧的新列中：

ablebitsregexextract语法

我们的自定义功能具有以下语法：

Ablebitsregexextract（参考，常规_expression）

在哪里：

• 参考（必需） - 对包含源字符串的单元格的引用。
• 常规_ expression （必需） - 要匹配的正则表达式。

重要说明！该函数仅在机器上使用，并安装了Excel的Ultimate Suite。

用法注释

要使您的学习曲线更加顺畅，并且您的体验更加愉快，请注意以下要点：

1. 要创建公式，您可以使用我们的正则工具，也可以使用Excel的插入功能对话框，或在单元格中键入完整功能名称。插入公式后，您可以像任何本机公式一样管理（编辑，复制或移动）。
2. 您在Regex Tools上输入的模式窗格转到2 ^nd参数。也可以将正则表达式保存在单独的单元格中。在这种情况下，只需使用单元格引用即可进行第二^个参数。
3. 该函数提取第一个发现的匹配。
4. 默认情况下，该函数对病例敏感。对于不敏感的匹配，请使用（？i）模式。
5. 如果找不到匹配项，则返回一个错误。

以下是在两个字符之间提取字符串

要在两个字符之间获取文字，您可以使用捕获组或查看曲目。

假设您正在寻找在括号之间提取文本。捕获组是最简单的方法。

模式1 ：\ [（。*？）\]

凭借积极的外观和lookahead，结果将完全相同。

模式2 ：（？

请注意，我们的捕获组（。一个没有问号的捕获小组（。*）将进行贪婪的搜索，并捕获从第一个[到最后一个]的一切。

使用A2中的模式，该公式如下：

=AblebitsRegexExtract(A5, $A$2)

如何获得所有比赛

如前所述，Ablebitsregexextract功能只能提取一个匹配。要获得所有匹配，您可以使用我们之前讨论过的VBA功能。但是，有一个警告-VBA RegeXP不支持捕获组，因此上述模式也将返回“边界”字符，在我们的情况下，括号。

=TEXTJOIN(" ", TRUE, RegExpExtract(A5, $A$2))

要摆脱支架，请使用此公式用空字符串（“”）代替它们：

=SUBSTITUTE(SUBSTITUTE(TEXTJOIN(", ", TRUE, RegExpExtract(A5, $A$2)), "]", ""),"[","")

为了获得更好的可读性，我们正在使用逗号进行定界符。

正则提取两个字符串之间的文本

我们为在两个字符之间拉出文本的方法还可以在两个字符串之间提取文本。

例如，要在“测试1”和“测试2”之间获得所有内容，请使用以下正则表达式。

模式：测试1（。*？）测试2

完整的公式是：

=AblebitsRegexExtract(A5, "test 1(.*?)test 2")

从URL提取域

即使使用正则表达式，从URL中提取域名也不是一项琐碎的任务。解决问题的关键要素是非捕捉组。根据您的最终目标，请选择以下以下一条。

获取一个完整的域名，包括子域

模式：（？：https？\：|^| \ s）\/\/（（？

要获得一个没有子域的第二级域

模式：（？：https？\：|^| \ s）\/\/（？

现在，让我们看看这些正则表达式如何在“ https://www.mobile.able.ablebits.com”的示例中作为示例URL：

• （？：https？\：|^| \ s） - 非捕捉组。它匹配但不捕获之前有以下一个的子字符串：https，http，string（^）的启动（^），一个whitespace字符（\ s）。包括最后两个项目以处理“ //google.com”之类的协议相关URL。
• \/ \/ - 两个前向斜线（每个斜线之前都有一个后斜切，以逃避前向斜线的特殊含义并从字面上解释它）。
• （？：[a-za-z \ d \ - \。] {2,255} \。）？ - 非捕获组以识别第三级，第四级等。域，如果有的话（我们的示例URL中的移动设备）。在第一个模式中，将其放置在较大的捕获组中，以将所有此类子域纳入提取中。子域可以长2到255个字符，因此{2,255}量词。
• （[a-za-z \ d \ - ] {1,63} \。[a-za-z] {2,24}） - 捕获组以提取第二级域（ ablebits ）和顶级域（ com ）。第二级域的最大长度为63个字符。当前存在的最长顶级域包含24个字符。

根据A2中的正则表达式，以下公式将产生不同的结果：

=AblebitsRegexExtract(A5, $A$2)

将所有子域提取完整域名的正则命名：

以下是提取没有子域的二级域：

这就是如何使用正则表达式在Excel中提取文本的部分。我感谢您阅读，并期待下周在我们的博客上与您见面！

可用下载

Excel Regex提取示例（.XLSM文件）Ultimate Suite试用版（.EXE文件）

以上是正则提取在Excel中提取字符串（一个或所有匹配）的详细内容。更多信息请关注PHP中文网其他相关文章！