java正则替换excel公式_给你的Excel增加正则处理函数,简直如虎添翼

本文主要是介绍java正则替换excel公式_给你的Excel增加正则处理函数,简直如虎添翼,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

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

小小明 凹凸数据

0e35e1cb5b2502bb7169ec07a724ffff.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 东南西北

效果如下:

a71ddb46ba1c083ba69341b08f63be59.png

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

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

小五87

张三丰98

东方不败76

杨过88

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

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

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

效果:

8cbe6dd35bb872cde56b41cf0fbd3a4e.png

正则搜索效果

对于一列数据:

联想/LENOVO

狮乐/SHILE

Midea/美的

联想/LENOVO

松下/Panasonic

红叶/RedLeaf

纳米亚

富士施乐/FujiXerox

佳印

佳能/CANON

TCL

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

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

最终结果:

07e07bc865c801b7d8f1e7b21ec4194b.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

效果:

ec32a7f724d60d19404117273a220989.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

效果:

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

即可得到:

中山古镇华艺集团路口

中山古镇华艺集团路口

中山古镇华艺集团路口

中山横栏富横东路

中山横栏富横东路

中山横栏富横东路

中山横栏富横东路

中山横栏富横东路

中山横栏富横东路

中山三角电信营业厅

中山三角高平营业厅

中山横栏中艺重工

中山横栏中艺重工

中山横栏中艺重工

中山横栏三沙商富路

中山横栏三沙商富路

中山横栏新丰物流

黄圃奥杰斯电器

效果:

7e67732680b3a0672bcded11b0523be8.png

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

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

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

c419e043685367f5404a230c559121a1.png

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

7d48d77c0526cf0b6acd1e508673689c.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

e16b77a2b148db3665626c8b19b2c979.png

然后按下Ctrl+S保存:

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

db40c4671ddd9d00bf9ce07b25a7a137.png

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

正则高级语法相关资料

非捕获组

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

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

09b3ff43be86ceffb58e11bb3d0ff953.png

940ebeeca65b09ac1e519f122720de4b.png

环视

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

a5d577e0d20e0a425116e2a4664f1538.png

获取捕获组

b33d58714cf30ca9a6bf2c19330d12db.png

完结,撒花!

干货分享,求个三连~

这篇关于java正则替换excel公式_给你的Excel增加正则处理函数,简直如虎添翼的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/294111

相关文章

Spring Security自定义身份认证的实现方法

《SpringSecurity自定义身份认证的实现方法》:本文主要介绍SpringSecurity自定义身份认证的实现方法,下面对SpringSecurity的这三种自定义身份认证进行详细讲解,... 目录1.内存身份认证(1)创建配置类(2)验证内存身份认证2.JDBC身份认证(1)数据准备 (2)配置依

利用python实现对excel文件进行加密

《利用python实现对excel文件进行加密》由于文件内容的私密性,需要对Excel文件进行加密,保护文件以免给第三方看到,本文将以Python语言为例,和大家讲讲如何对Excel文件进行加密,感兴... 目录前言方法一:使用pywin32库(仅限Windows)方法二:使用msoffcrypto-too

SpringBoot整合OpenFeign的完整指南

《SpringBoot整合OpenFeign的完整指南》OpenFeign是由Netflix开发的一个声明式Web服务客户端,它使得编写HTTP客户端变得更加简单,本文为大家介绍了SpringBoot... 目录什么是OpenFeign环境准备创建 Spring Boot 项目添加依赖启用 OpenFeig

Java Spring 中 @PostConstruct 注解使用原理及常见场景

《JavaSpring中@PostConstruct注解使用原理及常见场景》在JavaSpring中,@PostConstruct注解是一个非常实用的功能,它允许开发者在Spring容器完全初... 目录一、@PostConstruct 注解概述二、@PostConstruct 注解的基本使用2.1 基本代

springboot使用Scheduling实现动态增删启停定时任务教程

《springboot使用Scheduling实现动态增删启停定时任务教程》:本文主要介绍springboot使用Scheduling实现动态增删启停定时任务教程,具有很好的参考价值,希望对大家有... 目录1、配置定时任务需要的线程池2、创建ScheduledFuture的包装类3、注册定时任务,增加、删

SpringBoot整合mybatisPlus实现批量插入并获取ID详解

《SpringBoot整合mybatisPlus实现批量插入并获取ID详解》这篇文章主要为大家详细介绍了SpringBoot如何整合mybatisPlus实现批量插入并获取ID,文中的示例代码讲解详细... 目录【1】saveBATch(一万条数据总耗时:2478ms)【2】集合方式foreach(一万条数

IntelliJ IDEA 中配置 Spring MVC 环境的详细步骤及问题解决

《IntelliJIDEA中配置SpringMVC环境的详细步骤及问题解决》:本文主要介绍IntelliJIDEA中配置SpringMVC环境的详细步骤及问题解决,本文分步骤结合实例给大... 目录步骤 1:创建 Maven Web 项目步骤 2:添加 Spring MVC 依赖1、保存后执行2、将新的依赖

SpringBoot中配置文件的加载顺序解读

《SpringBoot中配置文件的加载顺序解读》:本文主要介绍SpringBoot中配置文件的加载顺序,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录SpringBoot配置文件的加载顺序1、命令⾏参数2、Java系统属性3、操作系统环境变量5、项目【外部】的ap

SpringBoot UserAgentUtils获取用户浏览器的用法

《SpringBootUserAgentUtils获取用户浏览器的用法》UserAgentUtils是于处理用户代理(User-Agent)字符串的工具类,一般用于解析和处理浏览器、操作系统以及设备... 目录介绍效果图依赖封装客户端工具封装IP工具实体类获取设备信息入库介绍UserAgentUtils

Spring 中的循环引用问题解决方法

《Spring中的循环引用问题解决方法》:本文主要介绍Spring中的循环引用问题解决方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录什么是循环引用?循环依赖三级缓存解决循环依赖二级缓存三级缓存本章来聊聊Spring 中的循环引用问题该如何解决。这里聊