小小明,「凹凸数据」专栏作者,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 东南西北
效果如下:
![45a7bde1aabbbe5fd67f987d36fbfc05.png](https://i-blog.csdnimg.cn/blog_migrate/ec7594f68cd2217ee8ab02e22dd9f754.png)
?:
表示当前括号内部是非捕获组。
再看一个简单的例子,对于一列数据:
小五87
张三丰98
东方不败76
杨过88
我们想将姓名和成绩分开,可以选中一排的二个单元格,然后输入公式:
=re_extract(A1,"(.+?)(\d+)")
然后按下Ctrl+shift+Enter(表示数组公式),即可得到如下结果。
效果:
![0c29b917ab45f2f5ca3941947851a2f0.png](https://i-blog.csdnimg.cn/blog_migrate/3239737bf91f0a43e6a91bc66ca195e4.png)
正则搜索效果
对于一列数据:
联想/LENOVO
狮乐/SHILE
Midea/美的
联想/LENOVO
松下/Panasonic
红叶/RedLeaf
纳米亚
富士施乐/FujiXerox
佳印
佳能/CANON
TCL
我们想提取其中的中文品牌,对于没有中文的才用英文,可以输入公式:
=re_find(A1,"[\u4e00-\u9fa5]+|^\w+$")
最终结果:
![b5a7d8eafd93c6ef58a9bb7618f86368.png](https://i-blog.csdnimg.cn/blog_migrate/df6cf3b43311bfb6e4b7505a9f0cd732.png)
当然正则搜索也支持数组公式,再看一个例子,对于下面一列数据:
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
效果:
![207cd6447178884a350e2b852b4996cb.png](https://i-blog.csdnimg.cn/blog_migrate/78ba89c88d81da99113ecca1cd881978.png)
正则替换效果
对于下面这列数据,我们希望仅保留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
效果:
![9d7b7dca8ad28286cddd1fce5a7acd65.png](https://i-blog.csdnimg.cn/blog_migrate/827ff9135ba4d7ac34203847c24bd653.png)
再举个例子,对于下面这列数据,想去掉所有的非中文字符和被括号括起来的字符:
中山-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_\-]+|\(.*\)", "")
即可得到:
中山古镇华艺集团路口
中山古镇华艺集团路口
中山古镇华艺集团路口
中山横栏富横东路
中山横栏富横东路
中山横栏富横东路
中山横栏富横东路
中山横栏富横东路
中山横栏富横东路
中山三角电信营业厅
中山三角高平营业厅
中山横栏中艺重工
中山横栏中艺重工
中山横栏中艺重工
中山横栏三沙商富路
中山横栏三沙商富路
中山横栏新丰物流
黄圃奥杰斯电器
效果:
![9fadb45bc53115d50447a3344f2776f8.png](https://i-blog.csdnimg.cn/blog_migrate/cee1548d6e3a1254f3a58a7ed21a426c.png)
用VBA实现上面三个函数并让其自动加载
好了,演示完效果,我们现在来看看如何开发这三个函数吧。
首先打开excel软件,点击开发工具->Visual Basic(或者直接按快捷键Alt+F11),打开VBA的编辑器:
![6bdc01cd71e9b3a6cb692d7d31513fca.png](https://i-blog.csdnimg.cn/blog_migrate/7f467d5e0ee6ee39f952b6b13c0c507d.png)
右键单击当前工作薄对象插入模块:
![bf0801c2161964b03f6617e091846285.png](https://i-blog.csdnimg.cn/blog_migrate/0145158d94a66efdcf6472dee3ed406f.png)
在模块中插入以下代码:
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
![96620632dd25a55248437447fc1b807f.png](https://i-blog.csdnimg.cn/blog_migrate/389133127941212b00b8bd5502c7d5a8.png)
然后按下Ctrl+S保存:
![0cf821a2151ae1e8ab266f36e2408916.png](https://i-blog.csdnimg.cn/blog_migrate/a8845ed9ba23756898085a63eb7eda06.png)
保存在个人宏工作簿:
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,文件名无所谓。
![a9bfcf72e1afed444f91ca2d545bc9bf.png](https://i-blog.csdnimg.cn/blog_migrate/4af5e3609b4883d123f87f48e4ebe672.png)
然后在这个电脑的任何时候,任何地方打开excel软件都可以直接使用上面开发好的正则处理函数了!
正则高级语法相关资料
非捕获组
功能:让某个圆括号只用于分组,而不捕获其中的内容。
方法:将(内容)改为(?:内容)
![67e70cf95f95259657e4918eba7ff081.png](https://i-blog.csdnimg.cn/blog_migrate/8fd328003bb6b6a83b57c45183e31ab2.png)
![bb8465f986dcae78ef938b0b2ee10403.png](https://i-blog.csdnimg.cn/blog_migrate/e17195caf126989a7d2936718fcebfc5.png)
环视
(?=abc)
是正则中表示位置的语法,用于表示一个位置,表示当前位置的右边必须是abc字符而不会匹配abc本身。
![4a1c2a89e420669364adef4d232f2195.png](https://i-blog.csdnimg.cn/blog_migrate/34f72e06984433a72f787083ee0046d1.png)
获取捕获组
![c2cf01b7460ec3039201ec1cc1dbd97e.png](https://i-blog.csdnimg.cn/blog_migrate/4203a1514d8f18cbfe277d54b4d17b85.png)
完结,撒花!
干货分享,求个三连~
![660916a1b0a43c89aabea32827caa5c8.png](https://i-blog.csdnimg.cn/blog_migrate/970da7c3734cd1b5e910b5953e58269c.png)
后台回复关键词「进群」,即刻加入读者交流群~
“在看”和“点赞”是对文章最好的支持