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

2023-10-28 15:20
文章标签 java 公式 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 @Scheduled注解及工作原理

Spring @Scheduled注解及工作原理

《Spring@Scheduled注解及工作原理》Spring的@Scheduled注解用于标记定时任务,无需额外库,需配置@EnableScheduling,设置fixedRate、fixedDe... 目录1.@Scheduled注解定义2.配置 @Scheduled2.1 开启定时任务支持2.2 创建
阅读更多...
SpringBoot中使用Flux实现流式返回的方法小结

SpringBoot中使用Flux实现流式返回的方法小结

《SpringBoot中使用Flux实现流式返回的方法小结》文章介绍流式返回（StreamingResponse）在SpringBoot中通过Flux实现,优势包括提升用户体验、降低内存消耗、支持长连... 目录背景流式返回的核心概念与优势1. 提升用户体验2. 降低内存消耗3. 支持长连接与实时通信在Sp
阅读更多...
Spring Boot 实现 IP 限流的原理、实践与利弊解析

Spring Boot 实现 IP 限流的原理、实践与利弊解析

《SpringBoot实现IP限流的原理、实践与利弊解析》在SpringBoot中实现IP限流是一种简单而有效的方式来保障系统的稳定性和可用性,本文给大家介绍SpringBoot实现IP限... 目录一、引言二、IP 限流原理2.1 令牌桶算法2.2 漏桶算法三、使用场景3.1 防止恶意攻击3.2 控制资源
阅读更多...
Mac系统下卸载JAVA和JDK的步骤

Mac系统下卸载JAVA和JDK的步骤

《Mac系统下卸载JAVA和JDK的步骤》JDK是Java语言的软件开发工具包,它提供了开发和运行Java应用程序所需的工具、库和资源,：本文主要介绍Mac系统下卸载JAVA和JDK的相关资料,需... 目录1. 卸载系统自带的 Java 版本检查当前 Java 版本通过命令卸载系统 Java2. 卸载自定
阅读更多...
springboot下载接口限速功能实现

springboot下载接口限速功能实现

《springboot下载接口限速功能实现》通过Redis统计并发数动态调整每个用户带宽,核心逻辑为每秒读取并发送限定数据量,防止单用户占用过多资源,确保整体下载均衡且高效,本文给大家介绍spring... 目录 一、整体目标 二、涉及的主要类/方法✅ 三、核心流程图解（简化） 四、关键代码详解1️⃣ 设置
阅读更多...
Java Spring ApplicationEvent 代码示例解析

Java Spring ApplicationEvent 代码示例解析

《JavaSpringApplicationEvent代码示例解析》本文解析了Spring事件机制,涵盖核心概念（发布-订阅/观察者模式）、代码实现（事件定义、发布、监听）及高级应用（异步处理、... 目录一、Spring 事件机制核心概念1. 事件驱动架构模型2. 核心组件二、代码示例解析1. 事件定义
阅读更多...
SpringMVC高效获取JavaBean对象指南

SpringMVC高效获取JavaBean对象指南

《SpringMVC高效获取JavaBean对象指南》SpringMVC通过数据绑定自动将请求参数映射到JavaBean,支持表单、URL及JSON数据,需用@ModelAttribute、@Requ... 目录Spring MVC 获取 JavaBean 对象指南核心机制：数据绑定实现步骤1. 定义 Ja
阅读更多...
javax.net.ssl.SSLHandshakeException:异常原因及解决方案

javax.net.ssl.SSLHandshakeException:异常原因及解决方案

《javax.net.ssl.SSLHandshakeException:异常原因及解决方案》javax.net.ssl.SSLHandshakeException是一个SSL握手异常,通常在建立SS... 目录报错原因在程序中绕过服务器的安全验证注意点最后多说一句报错原因一般出现这种问题是因为目标服务器
阅读更多...
Java实现删除文件中的指定内容

Java实现删除文件中的指定内容

《Java实现删除文件中的指定内容》在日常开发中,经常需要对文本文件进行批量处理,其中,删除文件中指定内容是最常见的需求之一,下面我们就来看看如何使用java实现删除文件中的指定内容吧... 目录1. 项目背景详细介绍2. 项目需求详细介绍2.1 功能需求2.2 非功能需求3. 相关技术详细介绍3.1 Ja
阅读更多...
springboot项目中整合高德地图的实践

springboot项目中整合高德地图的实践

《springboot项目中整合高德地图的实践》：本文主要介绍springboot项目中整合高德地图的实践,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一：高德开放平台的使用二：创建数据库（我是用的是mysql）三：Springboot所需的依赖（根据你的需求再
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2