‘Excel Regex教程：使用正则表达式掌握模式匹配’

你是否曾经需要将不同格式的电话号码标准化？从自由格式文本字段中提取关键词呢？介绍一下：正则表达式！正则表达式（RegEx）是用于在文本块中匹配字符串的强大而灵活的模式。在本文中，我们将讨论常见的正则表达式语法，如何在Excel中启用正则表达式，以及如何有效地使用它们。

什么是正则表达式？

正则表达式，通常缩写为”regex”或”regexp”，是一种定义搜索模式的方法，可用于各种文本操作任务，如搜索、解析和/或替换文本。正则表达式广泛用于编程、文本编辑器和其他需要模式匹配的软件任务中。Excel中的正则表达式语法基于.NET正则表达式引擎。

常见的正则表达式

正则表达式可以根据定义它们的特征进行分组。以下是您可能会遇到的常见正则表达式。

字面字符

正则表达式可以包含与其自身匹配的字面字符。例如，正则表达式”hello“将精确匹配字符串”hello“。

元字符

正则表达式还包括元字符，它们具有特殊的含义。一些常见的元字符包括：

  • .（点）：匹配除换行符以外的任意单个字符。
  • *：匹配前一个字符或组的零个或多个出现。
  • +：匹配前一个字符或组的一个或多个出现。
  • ?：匹配前一个字符或组的零个或一个出现。
  • |（管道）：作为逻辑或运算符，允许您指定多个选择项。
  • ()（括号）：将字符或子模式分组在一起。
  • []（方括号）：定义字符类，允许您匹配来自一组字符中的任何一个字符。
  • ^（插入符号）：匹配行或字符串的开头。
  • $：匹配行或字符串的结尾。
  • \（反斜杠）：转义元字符以字面匹配。

字符类

正则表达式（regex）中的字符类是特殊的符号，允许您匹配一组字符中的任何一个。它们用于通过提供一种简洁的方式来指定应匹配的字符组来简化正则表达式模式。您可以使用方括号[...]来定义字符类。例如，[aeiou]匹配任何元音字母。[A-Z]匹配任何大写字母。

量词

量词是用来指定目标字符串中特定模式、字符或字符类必须出现多少次才能匹配的结构。换句话说，它们指定了字符或组应该重复多少次。例如，a{3} 精确匹配连续出现三个 “a” 字符。 ab? 匹配 “a” 或 “ab”。

锚点

锚点是一种特殊字符，它们不匹配字符串中的任何字符。相反，它们匹配字符之前、之后或之间的位置。锚点用于确保正则表达式模式出现在文本中的特定位置。例如，^将模式锚定到字符串的开头，$将其锚定到末尾。

修改器

修改器是改变正则表达式引擎解释模式的字符。它们用于调整正则表达式匹配的行为，从而实现更灵活和可控的匹配。修改器可以影响匹配过程的各个方面，例如大小写敏感性、多行匹配以及特殊字符的解释方式。例如，i使模式不区分大小写，g使其全局匹配，因此它会匹配输入中的所有出现。

在Excel中使用正则表达式的应用

正则表达式在各种场景中都很有用：

数据验证

在大多数情况下，您需要验证数据以确保您查看的是正确的信息。一个经典的例子是电话号码。在美国，标准格式是(###) ###-####。然而，它们也可以被格式化为###-###-####, (+#) ### ### ####, ##########等等。正则表达式可以帮助识别文件中包含的信息是否确实是电话号码。以下正则表达式将返回匹配的10位电话号码，其中可选的国家代码为1到2位数字：(\+\d{1,2}\s)?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}$。

文本提取

长句子通常很难被传统的电子表格软件解析。为了提取特定的关键词，您可以使用正则表达式来实现。例如，要从访谈记录中提取用单引号括起来的书名，我们可以使用以下正则表达式：'(.*?)'。

文本操作

除了提取信息外，您还可以对其进行转换和标准化，以便进行报告。假设我们有兴趣提取美国邮政编码的前五位数字。我们可以使用正则表达式^\d{5}来替换包含邮政编码字段的所有实例。

分词

分词是将文本分割成单个的标记或单词的过程。可以使用正则表达式模式来定义分词文本的规则。例如，可以使用\s+来在空白字符上分割文本，或者使用\W+来在非单词字符（如标点符号）上分割文本。

有关其他模式的信息，请查看我们的Python正则表达式速查表。

在Excel中使用正则表达式

虽然Excel本身不支持正则表达式，但你可以通过编写自定义定义的VBA脚本（适用于Windows）或启用插件（适用于Mac）来启用它。让我们来看看如何启用一个正则表达式函数来从Excel的一列中提取文本。

对于Windows

要开始，请按下Alt+F11打开Visual Basic编辑器。您也可以通过工具 > 宏 > Visual Basic编辑器导航到它。然后，点击插入> 模块。

将以下函数粘贴到模块中：公共函数CustomRegExpExtract(inputText As String, regexPattern As String, Optional instanceNumber As Integer = 0, Optional matchCase As Boolean = True) As Variant
Dim textMatches() As String
Dim matchesIndex As Integer
Dim regex As Object
Dim matches As Object

On Error GoTo ErrHandler

CustomRegExpExtract = “”

‘ 创建一个正则表达式对象
Set regex = CreateObject(“VBScript.RegExp”)
regex.Pattern = regexPattern
regex.Global = True
regex.MultiLine = True

‘ 设置大小写敏感性
If matchCase Then
regex.IgnoreCase = False
Else
regex.IgnoreCase = True
End If

‘ 在输入文本上执行正则表达式模式
Set matches = regex.Execute(inputText)

If matches.Count > 0 Then
If instanceNumber = 0 Then
‘ 如果instanceNumber为0，则返回数组中的所有匹配项
ReDim textMatches(matches.Count – 1, 0)
For matchesIndex = 0 To matches.Count – 1
textMatches(matchesIndex, 0) = matches.Item(matchesIndex)
Next matchesIndex
CustomRegExpExtract = textMatches
Else
‘ 如果指定了instanceNumber，则返回该实例的匹配项
CustomRegExpExtract = matches.Item(instanceNumber – 1)
End If
End If

Exit Function

ErrHandler:
‘ 通过返回错误值来处理错误
CustomRegExpExtract = CVErr(xlErrValue)
End Function

‘ 修改自Patrick Matthews的原始脚本

将函数保存并返回到电子表格。现在，将工作簿另存为宏启用的文件，使用.xlsm文件扩展名。

在Excel工作表中使用该函数

通过在Excel工作簿中启用宏，您现在可以像在Excel中使用常规函数一样使用CustomRegExpExtract函数来解析复杂的字符串模式，其中：

  • inputText 是要解析的原始字符串；
  • regexPattern 是用于解析 inputText 的正则表达式模式；
  • instanceNumber 默认返回模式的所有实例，但可以输入一个整数来指定要返回的实例数；
  • matchCase 指定在匹配过程中是否考虑文本大小写（TRUE 或省略）或忽略大小写（FALSE）。

假设我们有兴趣从一个字段中提取电话号码。我们可以在任何单元格中输入正则表达式模式（在下面的例子中，它出现在单元格 A2 中）。在单元格 A6 中是您的自由格式字符串示例，”Sophia, 1111111111″。在单元格 B6 中，我们输入 =CustomRegExpExtract(A6,$A$2)。该函数通过将字符串与单元格 A2 中的正则表达式模式进行匹配来提取电话号码，并返回预期的结果 “1111111111”。

image1.png

这段文字的中文翻译如下：

对于Mac OS

不幸的是，Mac OS不支持MS VBScript Regular Expressions 5.5库。要在Mac上启用Excel中的复杂模式匹配，有两个选项：使用高级筛选或安装软件包。

image2.png

这段文字的中文翻译如下：

使用高级筛选

Excel中的高级筛选可以根据多个条件集合执行复杂的数据筛选操作。当处理大型数据集时，此功能特别有用。以下是如何在Excel中使用高级筛选：

  • 设置数据：确保您的数据以表格形式呈现，并具有清晰的标题。
  • 创建条件范围：在工作表中设置一个单独的区域来定义筛选的条件。该区域应具有与您要筛选的列相同的标题。在这些标题下方，指定筛选的条件。您可以在同一标题下输入多个条件以进行“或”逻辑，或者在不同行中输入以进行“与”逻辑。
  • 选择数据：单击数据集中的一个单元格，或选择要筛选的整个范围。
  • 设置筛选：单击Excel功能区中的数据选项卡，然后导航到高级筛选选项。在排序和筛选组中，单击高级。在高级筛选对话框中，选择是否要就地筛选列表或将结果复制到另一个位置。指定列表范围（您的数据）。指定条件范围（您设置筛选条件的位置）。单击确定应用筛选。

假设您有一个包含姓名、年龄、城市列的数据集。您想要筛选显示年龄超过30岁的纽约人或洛杉矶的任何人。您的条件范围可能如下所示：

姓名

年龄

城市

>30

‘

纽约

洛杉矶

安装插件

  • Kutools for Excel：Kutools是一个综合性的插件，包含许多功能，其中之一是使用正则表达式查找和替换数据。它在Excel的功能区中添加了一个新的选项卡，提供了各种工具，包括正则表达式函数。
  • RegEx Find/Replace：这是一个用于Excel的插件，允许您使用正则表达式查找和替换文本。它集成到Excel中，并提供比内置选项更高级的查找/替换功能。
  • PowerGREP：虽然不是直接的Excel插件，但PowerGREP可以与Excel文件一起使用。它是Windows上的一个强大的grep工具，允许对文本和二进制文件进行复杂的正则表达式操作。它更像是一个独立的应用程序，但可以与Excel一起用于高级的正则表达式处理。然而，它的价格不菲。单用户许可证的价格为159美元，100用户许可证的价格为5300美元。

一些插件可以直接在Excel中通过插入选项卡 > 获取插件找到。

其他的需要从外部来源下载，并按照它们的安装说明进行安装。

一些考虑因素：

  • 兼容性：确保插件与您的Excel版本和操作系统兼容。
  • 安全性：只从可靠的来源下载和安装插件。
  • 功能需求：考虑您需要完成的特定正则表达式任务，并选择最适合这些需求的插件。

结论

在大多数情况下，Excel中的原生函数如FIND、SEARCH和REPLACE可以满足您90%的文本操作需求。然而，在剩下的10%情况下，涉及更复杂的字符串模式时，正则表达式可以极大地帮助。要了解更多关于使用流行编程语言应用正则表达式的知识，请查看我们的Python正则表达式课程和R中级正则表达式课程。

为了迅速掌握Excel的所有知识，请查看我们的Excel基础技能轨迹，该轨迹将带领您掌握使用该工具所需的基本技能。

相关文章