给Excel 添加正则表达式regexp()函数

在Excel中添加自定义正则表达式函数：REGEXP()

在数据处理的过程中，正则表达式是非常强大的工具。WPS推出的REGEX函数家族让人眼前一亮，但对于老版本的Excel用户来说，我们可以通过VBA自定义一个强大的正则表达式函数——REGEXP()，从而实现字符串匹配、替换和测试等功能。

以下是REGEXP()函数的详细实现和使用方法。

功能概述

REGEXP()函数能够实现以下功能：

匹配模式：从字符串或数组中提取符合正则表达式的内容。

测试模式：检测字符串是否符合正则表达式。

替换模式：根据正则表达式对字符串内容进行替换。

此外，该函数支持以下特性：

多种数据类型：支持单元格、数组和字符串类型的输入。

灵活的结果返回：根据匹配结果返回单个值或全部匹配值。

大小写忽略：可选择忽略大小写。

代码实现

以下是完整的VBA代码：

'****************************************************************************
' 函数名称: REGEXP()
' 作用: 用于正则表达式匹配、替换、测试
' 参数: FindStr 进行匹配的字符串、数组或范围
'       MyPattern 正则表达式匹配规则
'       mode 操作模式，0表示执行匹配操作，1表示执行测试操作，2表示执行替换操作
'       ReplaceWith 为要替换匹配内容的字符串
'       n 表示匹配结果的第n个，当n=0时返回全部匹配结果
'       IgnoreCase 表示是否忽略大小写，默认为False
' 返回: 以数组的形式返回结果
' 作者: wmh163
' 日期: 20240622
' 修改:
'****************************************************************************
Public Function REGEXP(ByVal FindStr, ByVal myPattern, Optional ByVal mode As Integer = 0, Optional ByVal ReplaceWith As String, Optional ByVal n As Integer = 0, Optional ByVal IgnoreCase As Boolean = False)
    ' 声明变量
    Dim i As Long
    Dim RE As Object, allMatches As Object, aMatch As Object
    ' 创建正则表达式对象
    Set RE = CreateObject("vbscript.regexp")
    RE.IgnoreCase = IgnoreCase
    RE.Global = True
    Dim tempStr
    tempStr = FindStr
    ' 判断FindStr的类型
    If TypeName(tempStr) = "String" Then
        ' 如果FindStr为字符串类型
        If TypeName(myPattern) = "String" Then
            ' 如果MyPattern为字符串类型
            RE.Pattern = myPattern
            If mode = 0 Then
                ' 匹配模式
                Set allMatches = RE.Execute(FindStr)
                If allMatches.Count >= 1 Then
                    ' 如果匹配到至少一个结果
                    ReDim rslt(1 To 1, 1 To allMatches.Count)
                    For i = 1 To allMatches.Count
                        rslt(1, i) = allMatches(i - 1).Value
                    Next i
                    If n = 0 Then
                        REGEXP = rslt
                    End If
                    If n > 0 Then
                        REGEXP = rslt(1, n)
                    End If
                Else
                    ' 如果没有匹配到结果
                    REGEXP = CVErr(2042)
                End If
            ElseIf mode = 2 Then
                ' 替换模式
                REGEXP = RE.Replace(FindStr, ReplaceWith)
            ElseIf mode = 1 Then
                ' 测试模式
                REGEXP = RE.test(FindStr)
            End If
        ElseIf TypeName(myPattern) = "Variant()" Then
            ' 如果MyPattern为数组
            ReDim arr(1 To 1, 1 To UBound(myPattern))
            If mode = 0 Then
                ' 匹配模式
                For i = 1 To UBound(myPattern)
                    RE.Pattern = myPattern(i)
                    Set allMatches = RE.Execute(FindStr)
                    If allMatches.Count >= 1 Then
                        arr(1, i) = allMatches(0)
                    Else
                        arr(1, i) = CVErr(2042)
                    End If
                Next
            ElseIf mode = 2 Then
                ' 替换模式
                For i = 1 To UBound(myPattern)
                    RE.Pattern = myPattern(i)
                    arr(1, i) = RE.Replace(FindStr, ReplaceWith)
                Next
            ElseIf mode = 1 Then
                ' 测试模式
                For i = 1 To UBound(myPattern)
                    RE.Pattern = myPattern(i)
                    arr(1, i) = RE.test(FindStr)
                Next
            End If
            REGEXP = arr
        ElseIf TypeName(myPattern) = "Range" Then
            ' 如果MyPattern为范围
            brr = myPattern
            n = UBound(brr)
            ReDim arr(1 To 1, 1 To n)
            If mode = 0 Then
                ' 匹配模式
                For i = 1 To n
                    RE.Pattern = brr(i, 1)
                    Set allMatches = RE.Execute(FindStr)
                    If allMatches.Count >= 1 Then
                        arr(1, i) = allMatches(0)
                    Else
                        '未找到返回#N/A错误
                        arr(1, i) = CVErr(2042)
                    End If
                Next
            ElseIf mode = 2 Then
                ' 替换模式
                For i = 1 To n
                    RE.Pattern = brr(i, 1)
                    arr(1, i) = RE.Replace(FindStr, ReplaceWith)
                Next
            ElseIf mode = 1 Then
                ' 测试模式
                For i = 1 To n
                    RE.Pattern = brr(i, 1)
                    arr(1, i) = RE.test(FindStr)
                Next
            End If
            REGEXP = arr
        End If
    ElseIf TypeName(tempStr) = "Variant()" Then
        ' 如果FindStr为数组
        RE.Pattern = myPattern
        ReDim arr(1 To 1, 1 To UBound(tempStr))
        If mode = 0 Then
            ' 匹配模式
            For i = 1 To UBound(tempStr)
                Set allMatches = RE.Execute(tempStr(i, 1))
                If allMatches.Count >= 1 Then
                    arr(1, i) = allMatches(0)
                Else
                    arr(1, i) = CVErr(2042)
                End If
            Next
        ElseIf mode = 2 Then
            ' 替换模式
            For i = 1 To UBound(tempStr)
                arr(1, i) = RE.Replace(tempStr(i, 1), ReplaceWith)
            Next
        ElseIf mode = 1 Then
            ' 测试模式
            For i = 1 To UBound(tempStr)
                arr(1, i) = RE.test(tempStr(i, 1))
            Next
        End If
        REGEXP = arr
    End If
End Function

参数解析

FindStr：可以是单个字符串、数组或者Excel范围。

myPattern：正则表达式规则。

mode：操作模式：

0：匹配并返回结果。

1：测试是否匹配。

2：根据正则表达式替换内容。

ReplaceWith：替换时使用的字符串，仅在mode = 2时有效。

n：指定返回匹配结果的第几个；当为0时，返回全部结果。

IgnoreCase：是否忽略大小写，默认值为False。

**

使用示例

**

匹配模式：
在单元格A1输入"Hello 123 World"，使用公式：

=REGEXP(A1, “\d+”, 0)

结果为提取到的数字"123"。

测试模式：
检查A1是否包含数字：

=REGEXP(A1, “\d+”, 1)

结果为TRUE或FALSE。

替换模式：
替换A1中的数字为"[数字]"：

=REGEXP(A1, “\d+”, 2, “[数字]”)

结果为"Hello [数字] World"。

注意事项

错误值处理：当未匹配到结果时，函数会返回#N/A错误。

性能：处理大范围数据时，可能需要更多计算时间。

兼容性：需要确保Excel支持VBA，并启用了宏功能。

总结

通过上述VBA代码，我们为Excel添加了一个功能强大的正则表达式函数REGEXP()，能够显著提升数据处理的效率。这一函数特别适用于文本分析、数据清洗和复杂字符串处理任务。如果你有更多需求，还可以进一步扩展这一代码！