跳至主要内容

Excel 正则表达式教程：掌握模式匹配

探索正则表达式（RegEx）在 Excel 中用于模式匹配的强大功能。本综合指南将介绍如何标准化数据、提取关键词，以及执行高级文本处理。
· 12分钟 阅读

使用 AI 探索

您是否曾需要将不同格式的电话号码统一标准化？或者从自由文本字段中提取关键词？这就是 RegEx 大显身手的时候！RegEx（Regular Expressions，正则表达式）是一种功能强大且灵活的模式，用于在文本中匹配字符串。本文将介绍常见的正则表达式语法、如何在 Excel 中启用正则表达式，以及如何高效地使用它们。

什么是正则表达式？

正则表达式（常缩写为 "regex" 或 "regexp"）用于定义搜索模式，可用于搜索、解析和/或替换文本等各类文本处理任务。正则表达式广泛应用于编程、文本编辑器以及其他涉及模式匹配的软件中。Excel 中的正则表达式风格（语法）基于 .NET 正则表达式引擎。

常见的正则表达式要素

正则表达式可按其特性进行分组。以下是您将经常遇到的常见要素。

字面量字符

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

元字符

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

  • .（点）：匹配除换行符外的任意单个字符。
  • *：匹配前一个字符或分组出现零次或多次。
  • +：匹配前一个字符或分组出现一次或多次。
  • ?：匹配前一个字符或分组出现零次或一次。
  • |（竖线）：充当逻辑 OR，用于指定可选项。
  • ()（括号）：将字符或子模式分组在一起。
  • []（方括号）：定义字符类，匹配给定字符集合中的任一字符。
  • ^（脱字符）：匹配一行或字符串的起始位置。
  • $：匹配一行或字符串的结束位置。
  • \（反斜杠）：转义元字符以按字面含义匹配。

字符类

正则表达式中的字符类是一种特殊记法，用于从一组字符中匹配任意一个字符。它们通过简洁的方式指定应匹配的字符集合，从而简化正则模式。您可以使用方括号 [...] 来定义字符类。例如，[aeiou] 匹配任一元音字母；[A-Z] 匹配任一大写字母。

量词

量词用于指定在目标字符串中某个模式、字符或字符类需要出现的次数，即应重复的次数。例如，a{3} 精确匹配连续三个字母 "a"；ab? 匹配 "a" 或 "ab"。

锚点

锚点是特殊字符，本身不匹配任何具体字符，而是匹配字符之间、之前或之后的位置。它们用于确保正则模式出现在文本的特定位置。例如，^ 将模式锚定到字符串开头，$ 将其锚定到结尾。

修饰符

修饰符用于改变正则引擎解释模式的方式，借此调整匹配行为，提供更大的灵活性和控制力。修饰符会影响匹配过程的多个方面，如大小写敏感、多行匹配以及特殊字符的解释方式。例如，i 使模式大小写不敏感，g 使其为全局匹配，从而匹配输入中的所有出现。

正则表达式在 Excel 中的应用

RegEx 在多种场景下都很有用：

数据验证

大多数情况下，您需要先进行数据验证，以确保查看的是正确的信息。以电话号码为例：在美国，标准格式为（###) ###-####）。但它们也可能被写成 ###-###-####, (+#) ### ### ####, ########## 等形式。RegEx 可以帮助识别文件中的信息是否确实为电话号码。以下正则表达式将匹配 10 位电话号码，并可选匹配 1 到 2 位的国家代码：(\+\d{1,2}\s)?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}$。

文本提取

传统电子表格软件往往难以解析长句。若要提取特定关键词，您可以使用 RegEx 来完成。比如，要从访谈记录中提取以单引号括起的书名，我们可以使用如下正则表达式：'(.*?)'。

文本处理

除了提取信息之外，您也可以对其进行转换与标准化，以便于报告。假设我们希望提取美国邮政编码（ZIP code）的前五位数字。我们可以使用正则表达式 ^\d{5} 来替换包含邮编的字段中的所有匹配实例。

分词

分词是将文本拆分为独立的标记或单词的过程。可以使用正则模式来定义分词规则。例如，使用 \s+ 按空白字符拆分文本，或使用 \W+ 按非单词字符（如标点符号）拆分。

如需更多模式信息，请查看我们的Python 正则表达式速查表。

在 Excel 中使用 RegEx

虽然 Excel 并不原生支持 RegEx，但您可以通过在 Windows 上编写 VBA 脚本自定义函数，或在 Mac 上启用外接程序来实现。下面我们将演示如何启用一个正则函数，从 Excel 的某一列中提取文本。

Windows 版

首先，按 Alt+F11 打开 Visual Basic 编辑器。也可通过工具 > 宏 > Visual Basic 编辑器进入。然后单击 插入 > 模块。

将以下函数粘贴到模块中：

Public Function 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 = ""
    
    ' Create a regular expression object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = regexPattern
    regex.Global = True
    regex.MultiLine = True
    
    ' Set case sensitivity
    If matchCase Then
        regex.IgnoreCase = False
    Else
        regex.IgnoreCase = True
    End If
    
    ' Execute the regex pattern on the input text
    Set matches = regex.Execute(inputText)
    
    If matches.Count > 0 Then
        If instanceNumber = 0 Then
            ' If instanceNumber is 0, return all matches in an array
            ReDim textMatches(matches.Count - 1, 0)
            For matchesIndex = 0 To matches.Count - 1
                textMatches(matchesIndex, 0) = matches.Item(matchesIndex)
            Next matchesIndex
            CustomRegExpExtract = textMatches
        Else
            ' If instanceNumber is specified, return the match at that instance
            CustomRegExpExtract = matches.Item(instanceNumber - 1)
        End If
    End If
    
    Exit Function

ErrHandler:
    ' Handle errors by returning an error value
    CustomRegExpExtract = CVErr(xlErrValue)
End Function

' Modified from an original script by Patrick Matthews

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

在 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 功能区中单击数据选项卡，找到排序和筛选组中的高级。在“高级筛选”对话框中，选择在原位筛选还是将结果复制到其他位置。指定“列表区域”（您的数据）。指定“条件区域”（您设置筛选条件的区域）。单击“确定”应用筛选。

假设您有包含 Name、Age、City 三列的数据集，想要筛选出在 New York 且年龄大于 30 岁的人，或任何在 Los Angeles 的人。您的条件区域可能如下所示：

Name

Age

City

 

>30

New York

  

Los Angeles

安装外接程序

  • Kutools for Excel：Kutools 是一款功能全面的外接程序，其中包括使用正则表达式查找和替换数据的功能。它会在 Excel 功能区新增选项卡，提供广泛的工具，包括正则相关功能。
  • RegEx Find/Replace：这是一款允许您使用正则表达式查找和替换文本的 Excel 外接程序。它集成于 Excel，相比内置选项提供更强大的查找/替换功能。
  • PowerGREP：虽然并非直接的 Excel 外接程序，但 PowerGREP 可处理 Excel 文件。它是 Windows 上功能强大的 grep 工具，能够对文本和二进制文件执行复杂的正则操作。它更像是独立应用，但可与 Excel 配合用于高级正则处理。不过其价格不菲：单用户许可证为 159 美元，100 用户许可证高达 5,300 美元。

部分外接程序可在 Excel 内通过插入选项卡 > 获取外接程序直接找到。

其他外接程序需要从外部来源下载，并按照其安装说明进行配置。

几点注意事项：

  • 兼容性：确保外接程序与您的 Excel 版本和操作系统兼容。
  • 安全性：仅从可信来源下载并安装外接程序。
  • 功能需求：根据您需要完成的正则任务选择最合适的外接程序。

结语

在大多数情况下，Excel 的内置函数（如 FIND、SEARCH、REPLACE）就能满足 90% 的文本处理需求。但对于剩余 10% 的复杂字符串模式，正则表达式将大有裨益。想进一步了解如何在主流编程语言中应用正则表达式，可查看我们的课程：Python 正则表达式 和 R 语言进阶正则表达式。

若想全面掌握 Excel，请查看我们的Excel 基础技能路径，涵盖成为熟练用户所需的核心技能。

Chloe Lubin

作者

Chloe Lubin

白天做数据分析，夜晚讲故事。我热衷于用自己的知识弥合数据素养鸿沟，帮助新手顺利转入这一领域。我的理念是每天都要学习，哪怕只有5分钟！

主题

电子表格