WPS Excel启用正则表达式

WPS Excel启用正则表达式

新建一个空白表格文件

进入VB编辑器

插入模块

工具-引用-勾选正则表达式

(Microsoft VBScript Regular Express 5.5)

复制代码

Option Explicit

Public Function re_sub(sText As String, pattern As String, repl As String)
    Dim oRegExp As Object
    Set oRegExp = CreateObject("vbscript.regexp")
    With oRegExp
        .Global = True 'True表示匹配所有, False表示仅匹配第一个符合项
        .IgnoreCase = False '区分大小写
        .pattern = pattern
        re_sub = .Replace(sText, repl)
    End With
End Function

Public Function re_find(sText As String, pattern As String)
    Dim oRegExp As Object, match As Object, matches As Object
    Set oRegExp = CreateObject("vbscript.regexp")
    With oRegExp
        .Global = True 'True表示匹配所有, False表示仅匹配第一个符合项
        .IgnoreCase = True '不区分大小写
        .pattern = pattern
        Set matches = .Execute(sText)
    End With
    
    Dim d As Object
    Set d = CreateObject("Scripting.Dictionary")
    For Each match In matches
        d.Add match, Null
    Next
    re_find = d.keys
End Function


Public Function re_extract(sText As String, pattern As String)
    Dim oRegExp As Object, match As Object, matches As Object, i As Integer
    Set oRegExp = CreateObject("vbscript.regexp")
    With oRegExp
        .Global = True 'True表示匹配所有, False表示仅匹配第一个符合项
        .IgnoreCase = True '不区分大小写
        .pattern = pattern
        Set matches = .Execute(sText)(0).submatches
    End With
    
    Dim d As Object
    Set d = CreateObject("Scripting.Dictionary")
    For i = 0 To matches.Count - 1
        d.Add matches(i), Null
    Next
    re_extract = d.keys
    
End Function

保存为启用宏的模板文件

必须使用Excel软件保存为xlam加载宏的文件格式才可在下一步进行加载

加载宏

在开发工具菜单栏下选择加载项并选中保存的xlam文件即可加载

---

关于Excel办公,推荐方方格子插件
http://www.ffcell.com/home/products.aspx