专栏首页凹凸玩数据给你的Excel增加正则处理函数,简直如虎添翼

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

小小明,「凹凸数据」专栏作者,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 东南西北

效果如下:

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

再看一个简单的例子,对于一列数据:

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

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

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

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

效果:

正则搜索效果

对于一列数据:

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

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

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

最终结果:

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

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

效果:

正则替换效果

对于下面这列数据,我们希望仅保留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

效果:

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

中山-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_\-]+|\(.*\)", "")

即可得到:

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

效果:

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

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

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

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

在模块中插入以下代码:

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

然后按下Ctrl+S保存:

保存在个人宏工作簿:

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,文件名无所谓。

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

正则高级语法相关资料

非捕获组

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

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

环视

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

获取捕获组

完结,撒花!

干货分享,求个三连~

本文分享自微信公众号 - 凹凸数据(alltodata),作者:小小明

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-01-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 对不起,我把APP也给爬了

    最近群里很多小伙伴对爬取手机app和小程序感兴趣,今天本厨师将给大家呈现这道菜,供小伙伴们品尝。

    朱小五
  • matplotlib绘图的核心原理讲解

    matplotlib是基于Python语言的开源项目,旨在为Python提供一个数据绘图包。

    朱小五
  • 用python爬取4332条粽子数据进行分析,看哪个最受大家欢迎

    爬取淘宝数据,本次采用的方法是:Selenium控制Chrome浏览器自动化操作[1]。其实我们还可以利用Ajax接口来构造链接,但是非常繁琐(包含加密秘钥等)...

    朱小五
  • Centos8搭建本地Web服务器的实现步骤

    再浏览器输入内网ip地址(内网ip可以通过ifconfig查看).若出现以下页面则表示成功.

    砸漏
  • MySQL 参数调整实践之slave_pending_jobs_size_max篇

    MySQL 的各参数的值设置需根据操作系统硬件情况,操作系统参数情况及数据库其他参数情况而进行调整,本文将结合生成环境的异常情况介绍MySQL slave_pe...

    July
  • 网站安全检测 对thinkphp通杀漏洞利用与漏洞修复建议

    thinkphp在国内来说,很多站长以及平台都在使用这套开源的系统来建站,为什么会这么深受大家的喜欢,第一开源,便捷,高效,生成静态化html,第二框架性的易于...

    技术分享达人
  • 网站安全检测 网站漏洞修复 对thinkphp通杀漏洞利用与修复建议

    thinkphp在国内来说,很多站长以及平台都在使用这套开源的系统来建站,为什么会这么深受大家的喜欢,第一开源,便捷,高效,生成静态化html,第二框架性的易于...

    网站安全专家
  • 数据库 ID 生成方案:雪花算法

    今天介绍的雪花算法:Snowflake,可以让负责生成分布式 ID 的每台机器在每毫秒内生成不一样的 ID。Snowflake 是 Twitter 开源的分布式...

    happyJared
  • 垃圾收集器详解及参数配置

    client模式下默认的垃圾收集器组合,可通过-XX:+UseSerialGC强制开启。非常适合运行于客户端PC的小型应用程序,或者桌面应用程序(比如swing...

    ydymz
  • 可帮助完成集体决策的人工智能系统上线

    美国卡耐基梅隆大学发布消息称,可辅助集体决策的人工智能系统已经上线。 有争议的总统选举可能引起人们关于当下投票制度能否选出最佳候选人这一问题的质疑。尽管美国人的...

    人工智能快报

扫码关注云+社区

领取腾讯云代金券

,,