Excel Regex示例：使用公式中的正则表达式

永远无法理解为什么在Excel公式中不支持正则表达式？现在，它们是:)使用我们的自定义功能，您可以轻松地找到，替换，提取和删除与特定模式相匹配的字符串。

乍一看，Excel拥有文本字符串操作所需的一切。嗯...正则表达式呢？糟糕，Excel中没有内置的正则函数。但是没有人说我们不能创建自己的:)

• 什么是正则是什么？
• Excel中是否有正则功能？
• Excel Regex备忘单
• 自定义VBA REGEX功能
• 正则匹配功能
• REGEX提取功能
• 正则替换功能
• REGEX工具 - 不带VBA的Excel中的正则表达式

什么是正则表达？

正则表达式（又名REGEX或REGEXP ）是定义搜索模式的特殊编码字符序列。使用该模式，您可以在字符串中找到匹配的字符组合或验证数据输入。如果您熟悉通配符符号，则可以将Regexes视为通配符的高级版本。

正则表达式具有自己的语法，由特殊字符，操作员和构造组成。例如，[0-5]匹配从0到5的任何单个数字。

正则表达式用于许多编程语言，包括JavaScript和VBA。后者有一个特殊的Regexp对象，我们将使用该对象来创建我们的自定义功能。

Excel支持REGEX吗？

遗憾的是，Excel中没有内置的正则函数。为了能够在公式中使用正则表达式，您必须创建自己的用户定义功能（基于VBA或.NET）或安装支持Regexes的第三方工具。

Excel Regex备忘单

无论是正则模式非常简单还是非常复杂，它都是使用通用语法构建的。本教程并非旨在教您正则表达式。为此，在线有很多资源，从初学者的免费教程到高级用户的高级课程。

在下面，我们可以快速参考主要的正则表达式模式，以帮助您掌握基础知识。在研究进一步的例子时，它也可以用作您的备忘单。

如果您对正则表达式感到满意，则可以直接跳到Regexp功能。

人物

这些是匹配某些字符的最常用模式。

图案	描述	例子	比赛
。	通配符角色：匹配除换行符以外的任何单个角色	.ot	点，热，锅， @ot
\ d	数字字符：从0到9的任何一位数字	\ d	在A1B中，匹配1
\ d	任何不是数字的角色	\ d	在A1B中，匹配A和B
\ s	空格角色：空间，标签，新线路和马车返回	。\ s。	在3美分中，匹配3 C
\ s	任何非Whitespace字符	\ s	在30美分中，匹配30美分和美分
\ w	单词字符：任何ASCII字母，数字或下划线	\ w	在5_cats ***中，匹配5_cats
\ w	任何不是字母数字或下划线的角色	\ w	在5_cats ***中，匹配***
\ t	选项卡
\ n	新线	\ n \ d	在下面的两行字符串中，匹配10 5只猫10只狗
\ \	逃脱角色的特殊含义，因此您可以搜索它	\。\ w \。	逃脱了一个时期，因此您可以找到字面上的“”。弦中的角色先生，夫人，教授

角色类

使用这些模式，您可以匹配不同字符集的元素。

图案	描述	例子	比赛
[人物]	匹配括号中的任何单个字符	D [oi] g	狗和挖
[^字符]	匹配任何单个字符在括号中	d [^oi] g	匹配DAG，DUG ， D1G不匹配狗和挖掘
[从–到]	匹配括号之间的任何角色	[0-9] [AZ] [AZ]	任何单个数字从0到9的任何单个小写字母任何单个大写字母

量词

量词是指定要匹配的字符数的特殊表达式。量词始终适用于字符。

图案	描述	例子	比赛
*	零或更多事件	1a*	1，1a ， 1aa，1aaa ，等等。
	一次或多次发生	po	在锅中，匹配PO的穷人，匹配Poo
？	零或一次发生	路	路，罗德
*？	零或更多的发生，但尽可能少	1a*？	在1A ， 1AA和1AAA中，匹配1A
？	一次或多次发生，但尽可能少	po？	在锅和穷人中，匹配po
？	零或一次发生，但尽可能少	roa ??	在公路和杆上，搭配RO
{n}	匹配前面的模式n次	\ d {3}	正好3位数字
{n，}	匹配前面的模式n或更多次	\ d {3，}	3个或更多位数
{N，M}	匹配N和M时间之间的前面模式	\ d {3,5}	从3到5位数字

分组

分组结构用于从源字符串捕获子字符串，因此您可以对其进行一些操作。

句法	描述	例子	比赛
（图案）	捕获组：捕获匹配的子字符串并为其分配一个序数编号	（\ d）	在5只猫和10只狗中，捕获5 （第1组）和10只（第2组）
（？：图案）	非捕捉组：匹配一个组，但没有捕获	（\ d）（？：狗）	在5只猫和10只狗中，捕获10
\ 1	第1组的内容	（\ d）\（\ d）= \ 2 \ \ 1	匹配5 10 = 10 5 ，并捕获5和10 ，它们在捕获组中
\ 2	第2组的内容

锚

锚指定输入字符串中的位置，以查找匹配项。

锚	描述	例子	比赛
^	启动字符串 注意：[^内括号]的意思是“不”	^\ d	字符串开始时的任意数字。 在5只猫和10只狗中，匹配5
$	字符串的结尾	\ D $	字符串末端的任意数字。 在10加5中给出15个，比赛15
\ b	单词边界	\ bjoy \ b	将乔伊作为一个单独的词匹配，但不愉快。
\ b	不是单词边界	\ bjoy \ b	匹配愉快的喜悦，但不像一个单独的词相匹配。

交替（或）构造

交替操作数启用或逻辑，因此您可以匹配此元素或该元素。

构造	描述	例子	比赛
|	匹配任何由垂直条隔开的单个元素	（S | SH）Ells	在她出售海壳，销售和贝壳

环绕

当您想匹配某些或不遵循或之前的事物之前，Lookaround构造会有所帮助。这些表达式有时被称为“零宽度断言”或“零宽度匹配”，因为它们匹配位置而不是实际字符。

笔记。在VBA Regex风味中，不支持LookBehinds。

图案	描述	例子	比赛
（？=）	积极的lookahead	x（？= y）	匹配表达式x时y时y（即，如果x前面有y，则匹配）
（？！）	负面的lookahead	x（？！y）	匹配表达式x如果不紧随其后y
（？	积极的外观	（？	匹配表达式x之前的表达式x（即，如果x后面有y）
（？	负外观	（？	匹配表达式x当不在y之前

现在，您知道了必需品，让我们继续前进最有趣的部分 - 使用真实数据上的Regexes来解析字符串并找到所需的信息。如果您需要有关语法的更多详细信息，则有关正则表达语言的Microsoft指南可能会有所帮助。

excel的自定义正则函数

如前所述，Microsoft Excel没有内置的正则函数。为了启用正则表达式，我们创建了三个自定义VBA函数（又称用户定义的函数）。您可以从下面链接的页面或我们的示例工作簿中复制代码，然后粘贴到自己的Excel文件中。

VBA REGEXP功能如何工作

本节解释了内部力学，对于那些想确切知道后端发生什么的人来说可能很有趣。

要开始在VBA中使用正则表达式，您需要激活Regex对象参考库或使用CreateObject函数。为了节省您在VBA编辑器中设置参考的麻烦，我们选择了后一种方法。

Regexp对象具有4个属性：

• 模式- 是在输入字符串中匹配的模式。
• 全局- 控制是在输入字符串中找到所有匹配还是第一个匹配。在我们的功能中，将所有匹配项设置为真实。
• 多行- 确定是在多线字符串中还是仅在第一行中匹配跨线路断裂的模式。在我们的代码中，在每行搜索中都设置为真实。
• ignorecase-定义正则表达式是对病例敏感的（默认）还是不敏感的（设置为true）。在我们的情况下，这取决于您如何配置可选的match_case参数。默认情况下，所有功能都对病例敏感。

VBA REGEXP限制

Excel VBA实现了必需的正则表达方式，但它确实缺乏.NET，PERL，JAVA和其他Regex引擎中可用的许多高级功能。例如，VBA REGEXP不支持对诸如（？i）的内联修饰符（？i）用于不敏感的匹配或（？m）多行模式，lookBehinds，posix类，以等。

Excel Regex匹配功能

REGEXPMATCH函数搜索与正则表达式匹配的文本的输入字符串，如果找到匹配，则返回true，否则为false。

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

在哪里：

• 文本（必需） - 一个或多个搜索的字符串。
• 模式（必需） - 匹配的正则表达式。
• match_case （可选） - 匹配类型。真实或省略 - 对病例敏感；错误 - 不敏感的情况

该函数的代码在这里。

示例：如何使用正则表达式匹配字符串

在下面的数据集中，假设您要标识包含SKU代码的条目。

鉴于每个SKU以2个大写字母开头，然后是连字符，然后是4位数字，您可以使用以下表达式匹配它们。

模式：\ b [az] {2} - \ d {4} \ b

其中[az] {2}表示从a到z的任何2个大写字母，而\ d {4}表示从0到9的任何4个数字。一个单词边界\ b表示SKU是一个单独的单词，而不是较大字符串的一部分。

建立模式后，开始键入像平常一样的公式，函数的名称将显示在Excel Autocomplete建议的列表中：

假设原始字符串在A5中，则该公式如下：

=RegExpMatch(A5, "\b[AZ]{2}-\d{3}\b")

为了方便起见，您可以在单独的单元格中输入正则表达式，并使用绝对参考（$ a $ 2）进行模式参数。这样可以确保当您将公式复制到其他单元格时，小区地址将保持不变：

=RegExpMatch(A5, $A$2)

要显示您自己的文本标签，而不是True和False，请在IF函数中nest RegexPmatch，并在value_if_true和value_if_if_false参数中指定所需的文本：

=IF(RegExpMatch(A5, $A$2), "Yes", "No")

有关更多公式示例，请参阅：

• 如何使用正则表达式匹配字符串
• Excel数据验证带有正则验证

Excel Regex提取功能

Regexpextract函数搜索与正则表达式匹配并提取所有匹配或特定匹配的子字符串。

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

在哪里：

• 文本（必需） - 要搜索的文本字符串。
• 模式（必需） - 匹配的正则表达式。
• instance_num （可选） - 指示要提取的实例的序列号。如果省略，则返回所有找到的匹配项（默认）。
• match_case （可选） - 定义要匹配（true还是省略）还是忽略（false）文本案例。

您可以在此处获取功能的代码。

示例：如何使用正则表达式提取字符串

以我们的示例进一步，让我们提取发票号码。为此，我们将使用与任何7位数字匹配的非常简单的正则等级：

模式：\ b \ d {7} \ b

将图案放入A2中，您将通过这种紧凑而优雅的公式完成工作：

=RegExpExtract(A5, $A$2)

如果模式匹配，则公式将提取发票号，如果找不到匹配 - 什么也不会返回。

有关更多示例，请参阅：如何使用Regex在Excel中提取字符串。

Excel Regex替换功能

Regexpreplace函数替换了与您指定的文本相匹配的值。

regexpreplace（文本，模式，替换，[instance_num]，[match_case]）

在哪里：

• 文本（必需） - 要搜索的文本字符串。
• 模式（必需） - 匹配的正则表达式。
• 替换（必需） - 用文本替换匹配子字符串的文本。
• instance_num （可选） - 要替换​​的实例。默认值为“所有匹配”。
• match_case （可选） - 控制要匹配（true还是省略）或忽略（false）文本案例。

该功能的代码可在此处提供。

示例：如何使用Regexes替换或删除字符串

我们的一些记录包含信用卡号。此信息是机密的，您可能需要用某些东西替换或完全删除它。这两个任务都可以在Regexpreplace功能的帮助下完成。如何？在第二种情况下，我们将替换为一个空字符串。

在我们的示例表中，所有卡号都有16位数字，这些数字用4组写成，这些数字是用空格分开的。为了找到它们，我们使用此正则表达式复制模式：

模式：\ b \ d {4} \ d {4} \ d {4} \ d {4} \ b

为了替换，使用以下字符串：

替换：xxxx xxxx xxxx xxxx

这是一个完整的公式，可以用不敏感的信息替换信用卡号：

=RegExpReplace(A5, "\b\d{4} \d{4} \d{4} \d{4}\b", "XXXX XXXX XXXX XXXX")

在单独的单元格（A2和B2）中的Regex和更换文本的情况下，该公式同样效果很好：

在Excel中，“删除”是“替换”的特殊情况。要删除信用卡号，只需使用一个空字符串（“”）进行替换参数：

=RegExpReplace(A5, "\b\d{4} \d{4} \d{4} \d{4}\b", "")

提示。要在结果中获取空线的钻机，您可以使用另一个RegexPreplace功能，如以下示例所示：如何使用Regex删除空白行。

有关更多信息，请参阅：

• 如何使用Regex替换Excel中的字符串
• 如何使用正则拆除字符串
• 如何使用Regexes剥离空格

匹配，提取，替换和删除子字符串的正则工具

我们的Ultimate Suite的用户可以在没有在其工作簿中插入一行代码的情况下获得正则表达式的所有功能。所有必要的代码都是由我们的开发人员编写的，并且在安装过程中将其集成在您的Excel中。

与上面讨论的VBA函数不同，Ultimate Suite的功能是基于.NET的，这给出了两个主要优势：

1. 您可以在普通.xlsx工作簿中使用正则表达式，而无需添加任何VBA代码并必须将其保存为宏观启用文件。
2. 。

如何在Excel中使用Regex

安装了Ultimate Suite后，在Excel中使用正则表达式与这两个步骤一样简单：

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

   

2. 在Regex工具窗格上，请执行以下操作：
   • 选择源数据。
   • 输入您的正则方式。
   • 选择所需的选项：匹配，提取，删除或更换。
   • 要将结果作为公式而不是价值，请选择插入作为公式复选框。
   • 点击动作按钮。

   例如，要从单元格A2：A6删除信用卡号，我们配置了这些设置：

   

在Trice中，Ablebitsregex功能将插入原始数据右侧的新列中。在我们的情况下，公式是：

=AblebitsRegexRemove(A2, "\b\d{4} \d{4} \d{4} \d{4}\b")

公式出现后，您可以像任何本机公式一样编辑，复制或移动它。

如何直接在单元格中插入正则公式

无需使用加载项的接口即可直接将Ablebitsregex函数直接插入单元格。以下是：

1. 单击公式栏上的FX按钮或“公式”选项卡上的插入功能。
2. 在“插入功能”对话框中，选择“ ablebitsudfs”类别，选择感兴趣的函数，然后单击“确定”。

   

3. 定义函数的论点，就像您通常这样做，然后单击确定。完毕！

有关更多信息，请参阅Excel的Regex工具。

这就是如何使用正则表达式匹配，提取，替换和删除Excel单元格中的文本的方法。我感谢您阅读，并期待下周在我们的博客上与您见面！

可用下载

Excel Regex-公式示例（.XLSM文件）Ultimate Suite-试用版（.EXE文件）

以上是Excel Regex示例：使用公式中的正则表达式的详细内容。更多信息请关注PHP中文网其他相关文章！