给你的Excel增加正则处理函数，简直如虎添翼

叁歲伎倆 2022-11-19 12:47 212阅读 0赞

![format_png][]

> 小小明，「凹凸数据」专栏作者，Pandas数据处理专家，致力于帮助无数数据从业者解决数据处理难题。

凹凸们，大家好

我们都知道Pandas里支持正则替换比较舒服，但是Excel却没有一个可以支持正则的函数！！！

不过我发现借助VBA就可以**在Excel实现正则的抽取、搜索和替换**，简直如虎添翼有没有~今天我要给大家分享一个我自己用VBA编写的神器，让你的Excel能够直接支持正则。看完全文，如果有收获，别忘了点赞支持一下

先看看效果吧：

## 正则抽取效果 ##

对于一列数据：

中楼层(共9层)|2007年建|1室1厅|24.78平米|北
    地下室|2014年建|1室0厅|39.52平米|东
    底层(共2层)5室3厅|326.56平米|东南西北

我们想提取出其中的 层、楼层数、建筑年份、户型、大小和方向，我们可以选中一排的六个单元格，然后输入公式：

=re_extract(A1,"([^|(]+)(?:$共(\d+)层$)?(?:\|(\d{4})年建\|)?(\d室\d厅)\|([\d.]+)平米\|([东南西北]+)")

然后按下Ctrl+shift+Enter(表示数组公式)，即可得到如下结果：

中楼层 9 2007 1室1厅 24.78 北
    地下室 0 2014 1室0厅 39.52 东
    底层 2 0 5室3厅 326.56 东南西北

效果如下：

![format_png 1][]

`?:`表示当前括号内部是非捕获组。

再看一个简单的例子，对于一列数据：

小五87
    张三丰98
    东方不败76
    杨过88

我们想将姓名和成绩分开，可以选中一排的二个单元格，然后输入公式：

=re_extract(A1,"(.+?)(\d+)")

然后按下Ctrl+shift+Enter(表示数组公式)，即可得到如下结果。

效果：

![format_png 2][]

## 正则搜索效果 ##

对于一列数据：

联想/LENOVO
    狮乐/SHILE
    Midea/美的
    联想/LENOVO
    松下/Panasonic
    红叶/RedLeaf
    纳米亚
    富士施乐/FujiXerox
    佳印
    佳能/CANON
    TCL

我们想提取其中的中文品牌，对于没有中文的才用英文，可以输入公式：

=re_find(A1,"[\u4e00-\u9fa5]+|^\w+$")

最终结果：

![format_png 3][]

当然正则搜索也支持数组公式，再看一个例子，对于下面一列数据：

ENBCUCPFunction=280419,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=279719,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280196,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280198,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280219,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280228,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280242,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=279519,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=279619,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=279633,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280032,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280382,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=279731,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280019,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=279819,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280319,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280191,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280194,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=460-01_280192,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280197,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280199,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=279419,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=279488,CULTE=1,RATFreq=1,EutranFreqLTE=18

我们需要取出所有的ENBCUCPFunction/CULTE/RATFreq/EutranFreqLTE四个字段对应的值，可以选中一排的四个单元格，然后输入公式：

=re_find(A1,"[-_\d]+")

即可得到结果：

280419 1 1 18
    279719 1 1 18
    280196 1 1 18
    280198 1 1 18
    280219 1 1 18
    280228 1 1 18
    280242 1 1 18
    279519 1 1 18
    279619 1 1 18
    279633 1 1 18
    280032 1 1 18
    280382 1 1 18
    279731 1 1 18
    280019 1 1 18
    279819 1 1 18
    280319 1 1 18
    280191 1 1 18
    280194 1 1 18
    460-01_280192 1 1 18
    280197 1 1 18
    280199 1 1 18
    279419 1 1 18
    279488 1 1 18

效果：

![format_png 4][]

## 正则替换效果 ##

对于下面这列数据，我们希望仅保留EutranFreqLTE对应的值，多个值用;拼接：

ENBCUCPFunction=280419,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=279719,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280196,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280198,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280219,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280228,CULTE=1,RATFreq=1,EutranFreqLTE=18;ENBCUCPFunction=280228,CULTE=1,RATFreq=1,EutranFreqLTE=19
    ENBCUCPFunction=280242,CULTE=1,RATFreq=1,EutranFreqLTE=18;ENBCUCPFunction=280242,CULTE=1,RATFreq=1,EutranFreqLTE=19
    ENBCUCPFunction=279519,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=279619,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=279633,CULTE=1,RATFreq=1,EutranFreqLTE=18;ENBCUCPFunction=279633,CULTE=1,RATFreq=1,EutranFreqLTE=19
    ENBCUCPFunction=280032,CULTE=1,RATFreq=1,EutranFreqLTE=18;ENBCUCPFunction=280032,CULTE=1,RATFreq=1,EutranFreqLTE=19
    ENBCUCPFunction=280382,CULTE=1,RATFreq=1,EutranFreqLTE=18;ENBCUCPFunction=280382,CULTE=1,RATFreq=1,EutranFreqLTE=19
    ENBCUCPFunction=279731,CULTE=1,RATFreq=1,EutranFreqLTE=18;ENBCUCPFunction=279731,CULTE=1,RATFreq=1,EutranFreqLTE=19
    ENBCUCPFunction=280019,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=279819,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280319,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280191,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280194,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=460-01_280192,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280197,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=280199,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=279419,CULTE=1,RATFreq=1,EutranFreqLTE=18
    ENBCUCPFunction=279488,CULTE=1,RATFreq=1,EutranFreqLTE=18;ENBCUCPFunction=279488,CULTE=1,RATFreq=1,EutranFreqLTE=19

可以输入公式：

=re_sub(A1,".+?(\d+)(;|$)", "$1$2")

得到结果：

18
    18
    18
    18
    18
    18;19
    18;19
    18
    18
    18;19
    18;19
    18;19
    18;19
    18
    18
    18
    18
    18
    18
    18
    18
    18
    18;19

效果：

![format_png 5][]

再举个例子，对于下面这列数据，想去掉所有的非中文字符和被括号括起来的字符：

中山-Z-古镇华艺集团路口-280308-1-2-OF
    中山-ZD-古镇华艺集团路口-280308-2-1-OF
    中山-Z-古镇华艺集团路口-280308-2-2-OF
    中山-ZD-横栏富横东路-280227-1-1-OF
    中山-Z-横栏富横东路-280227-1-2-OF
    中山-ZD-横栏富横东路-280227-2-1-OF
    中山-Z-横栏富横东路-280227-2-2-OF
    中山-ZD-横栏富横东路-280227-3-1-OF
    中山-Z-横栏富横东路-280227-3-2-OF
    中山-Z-三角电信营业厅(室分QCELL)-278903-1-1-MF
    中山-Z-三角高平营业厅(室分QCELL)-278902-1-1-MF
    中山-ZD-横栏中艺重工-280009-1-1-OF
    中山-ZD-横栏中艺重工-280009-2-1-OF
    中山-ZD-横栏中艺重工-280009-3-1-OF
    中山-Z-横栏三沙商富路-279966-1-2-OF
    中山-ZD-横栏三沙商富路-279966-1-1-OF
    中山-ZD-横栏新丰物流-279974-1-1-OF
    黄圃奥杰斯电器LTGX_3_F

输入一下公式：

=re_sub(A1,"[A-Z0-9_\-]+|$.*$", "")

即可得到：

中山古镇华艺集团路口
    中山古镇华艺集团路口
    中山古镇华艺集团路口
    中山横栏富横东路
    中山横栏富横东路
    中山横栏富横东路
    中山横栏富横东路
    中山横栏富横东路
    中山横栏富横东路
    中山三角电信营业厅
    中山三角高平营业厅
    中山横栏中艺重工
    中山横栏中艺重工
    中山横栏中艺重工
    中山横栏三沙商富路
    中山横栏三沙商富路
    中山横栏新丰物流
    黄圃奥杰斯电器

效果：

![format_png 6][]

## 用VBA实现上面三个函数并让其自动加载 ##

好了，演示完效果，我们现在来看看如何开发这三个函数吧。

首先打开excel软件，点击开发工具->Visual Basic（或者直接按快捷键Alt+F11），打开VBA的编辑器:

![format_png 7][]

右键单击当前工作薄对象插入模块：

![format_png 8][]

在模块中插入以下代码：

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

![format_png 9][]

然后按下Ctrl+S保存：

![format_png 10][]

保存在个人宏工作簿：

C:\Users\{userName}\AppData\Roaming\Microsoft\Excel\XLSTART\

对于家庭和学生版2016，个人宏工作簿在：

C:\Users\{userName}\AppData\Local\Packages\Microsoft.Office.Desktop_8wekyb3d8bbwe\LocalCache\Roaming\Microsoft\Excel\XLSTART

\{userName\}表示你当前的用户名。

格式为xlam，文件名无所谓。

![format_png 11][]

然后在这个电脑的任何时候，任何地方打开excel软件都可以直接使用上面开发好的正则处理函数了！

## 正则高级语法相关资料 ##

### 非捕获组 ###

功能：让某个圆括号只用于分组，而不捕获其中的内容。

方法：将(内容)改为(?:内容)

![format_png 12][] ![format_png 13][]

### 环视 ###

`(?=abc)`是正则中表示位置的语法，用于表示一个位置，表示当前位置的右边必须是abc字符而不会匹配abc本身。

![format_png 14][]

### 获取捕获组 ###

![format_png 15][]

完结，撒花！

干货分享，求个三连~

![format_png 16][]

**后台回复关键词「****进群****」，即刻加入读者交流群~**

![format_png 17][]

“**在看**”和“**点赞**”是对文章最好的支持** ![format_png 18][]**

[format_png]: /images/20221119/71fddd8a4fc3405b80823fc05e4b64ef.png
[format_png 1]: /images/20221119/5dc9b3527bae4926999d89b6c5496712.png
[format_png 2]: /images/20221119/6ef741e5698540a784b2294a9a073217.png
[format_png 3]: /images/20221119/016142ab3e864b519eec532dc89e0c94.png
[format_png 4]: /images/20221119/1073be7f88df4274ad55ffdf4455e42b.png
[format_png 5]: /images/20221119/f0c1d8bb696b46b4b747c20f9cf55afd.png
[format_png 6]: /images/20221119/84ea289776004d15bf90a358358c7655.png
[format_png 7]: /images/20221119/7ff70014e09c4e41aac02bed18301bfc.png
[format_png 8]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy90WFlpY3Q0MHhmTGc2TkNpY0NidzNEc2NSNjBYQmtnVGljOU1GZUlrR29WeXF1TkRza2VkSDUySUVlN1F1dkFqUEVpY0Z0ME1JNm5EbExNV1hHVmlhNVFVMmVRLzY0MA?x-oss-process=image/format,png
[format_png 9]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy90WFlpY3Q0MHhmTGc2TkNpY0NidzNEc2NSNjBYQmtnVGljOWlhTFRSbDJSeVdzMXdvaHBHMWQzQlh5cXF3VG41ZEtpYnZvTEU3R29VVmlhRExIMEMxQ1dWYjBBdy82NDA?x-oss-process=image/format,png
[format_png 10]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy90WFlpY3Q0MHhmTGc2TkNpY0NidzNEc2NSNjBYQmtnVGljOVlyV25nMTNOcjRKeDZiTXRKN2ljcjZQSWliMzlGUUdVQWZRRldLb1NaNTM0OVE0YnhXTUx1bW1nLzY0MA?x-oss-process=image/format,png
[format_png 11]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy90WFlpY3Q0MHhmTGc2TkNpY0NidzNEc2NSNjBYQmtnVGljOXJON0drdmljZEpJajczbTFQcnlwakxqSVBqdmhoT2R6UHZuaWJ2ejJSZmZzV1I0cGlhV1JjNnpVUS82NDA?x-oss-process=image/format,png
[format_png 12]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy90WFlpY3Q0MHhmTGc2TkNpY0NidzNEc2NSNjBYQmtnVGljOTJ6a1ZQMDkzR0dTSm0zUGp1SGlhZUNpYVdXVVRtakFPd2gzd2lhOXJkdWVrbm9HTW1zbzVkd2o2US82NDA?x-oss-process=image/format,png
[format_png 13]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy90WFlpY3Q0MHhmTGc2TkNpY0NidzNEc2NSNjBYQmtnVGljOWZoN3RaaldseHJNdXlXMGI2eUtmU2ZGUmMyWjJGV25ldnB5OGR0a0lpYm83QzZjeGhSVlRpYmx3LzY0MA?x-oss-process=image/format,png
[format_png 14]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy90WFlpY3Q0MHhmTGc2TkNpY0NidzNEc2NSNjBYQmtnVGljOTJlVDdFZWh4S3I0U1lRdjZlVUNHMVBDaWNTV2ljemp3eXF2djdiemFJSDBJeXR3elRQMGt0RzJRLzY0MA?x-oss-process=image/format,png
[format_png 15]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy90WFlpY3Q0MHhmTGc2TkNpY0NidzNEc2NSNjBYQmtnVGljOWZXUmc3RW1adjZoaWFNaWFsdkRwc2ZpY3Z2UjQzamFGYjBNSk9XUFBxVXc4UUtWdjFtVmlhR053YWcvNjQw?x-oss-process=image/format,png
[format_png 16]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9iOTZDaWJDdDcwaWFiaWFuSUhpYzFoQkFBcDg0aHBNQ2R6NVZpYWFqR3Q2QzJsWGFBWDNpYTExbXR5bnVlQndDckMyRHd1M0FGRmRJd1J3bjM0czJSTUdTMmtWQS82NDA?x-oss-process=image/format,png
[format_png 17]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi90WFlpY3Q0MHhmTGdpYWZHRm1XdFZWSEZWSzdpYlJ0eUVKT1hNczV2VjJBdVZIOFhVOTJKSlA0Y3dxbHRVSzBTTndDS01hZkNsMm9ZV3B4NjN1RXkyWDE5dy82NDA?x-oss-process=image/format,png
[format_png 18]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi9tbUYwclRqTW1tS1pJVUlMWFRJdEVldDZpYk05MGxJV2hLaWJIR25OZHlRaWN3NXpIQ2ptakl0dUltVUhIZ0Iyb05KNWNxNDFwOXhvbHYzY0QzcHVKTlo1dy82NDA?x-oss-process=image/format,png