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。

相关文章

JVM 的类初始化机制

JVM 的类初始化机制

前言 当你在 Java 程序中new对象时，有没有考虑过 JVM 是如何把静态的字节码（byte code）转化为运行时对象的呢，这个问题看似简单，但清楚的同学相信也不会太多，这篇文章首先介绍 JVM 类初始化的机制，然后给出几个易出错的实例来分析，帮助大家更好理解这个知识点。 JVM 将字节码转化为运行时对象分为三个阶段，分别是：loading 、Linking、initialization
阅读更多...
Spring Security 基于表达式的权限控制

Spring Security 基于表达式的权限控制

前言 spring security 3.0已经可以使用spring el表达式来控制授权，允许在表达式中使用复杂的布尔逻辑来控制访问的权限。 常见的表达式 Spring Security可用表达式对象的基类是SecurityExpressionRoot。 表达式描述hasRole([role])用户拥有制定的角色时返回true （Spring security默认会带有ROLE_前缀）,去
阅读更多...
浅析Spring Security认证过程

浅析Spring Security认证过程

类图 为了方便理解Spring Security认证流程，特意画了如下的类图，包含相关的核心认证类 概述 核心验证器 AuthenticationManager 该对象提供了认证方法的入口，接收一个Authentiaton对象作为参数; public interface AuthenticationManager {Authentication authenticate(Authenti
阅读更多...
Spring Security--Architecture Overview

Spring Security--Architecture Overview

1 核心组件 这一节主要介绍一些在Spring Security中常见且核心的Java类，它们之间的依赖，构建起了整个框架。想要理解整个架构，最起码得对这些类眼熟。 1.1 SecurityContextHolder SecurityContextHolder用于存储安全上下文（security context）的信息。当前操作的用户是谁，该用户是否已经被认证，他拥有哪些角色权限…这些都被保
阅读更多...
Spring Security基于数据库验证流程详解

Spring Security基于数据库验证流程详解

Spring Security 校验流程图 相关解释说明（认真看哦） AbstractAuthenticationProcessingFilter 抽象类 /*** 调用 #requiresAuthentication(HttpServletRequest, HttpServletResponse) 决定是否需要进行验证操作。* 如果需要验证，则会调用 #attemptAuthentica
阅读更多...
Spring Security 从入门到进阶系列教程

Spring Security 从入门到进阶系列教程

Spring Security 入门系列 《保护 Web 应用的安全》 《Spring-Security-入门（一）：登录与退出》 《Spring-Security-入门（二）：基于数据库验证》 《Spring-Security-入门（三）：密码加密》 《Spring-Security-入门（四）：自定义-Filter》 《Spring-Security-入门（五）：在 Sprin
阅读更多...
Java架构师知识体认识

Java架构师知识体认识

源码分析 常用设计模式 Proxy代理模式Factory工厂模式Singleton单例模式Delegate委派模式Strategy策略模式Prototype原型模式Template模板模式 Spring5 beans 接口实例化代理Bean操作 Context Ioc容器设计原理及高级特性Aop设计原理Factorybean与Beanfactory Transaction 声明式事物
阅读更多...
Java进阶13讲__第12讲_1/2

Java进阶13讲__第12讲_1/2

多线程、线程池 1.  线程概念 1.1  什么是线程 1.2  线程的好处 2.   创建线程的三种方式 注意事项 2.1  继承Thread类 2.1.1 认识  2.1.2  编码实现  package cn.hdc.oop10.Thread;import org.slf4j.Logger;import org.slf4j.LoggerFactory
阅读更多...
JAVA智听未来一站式有声阅读平台听书系统小程序源码

JAVA智听未来一站式有声阅读平台听书系统小程序源码

智听未来，一站式有声阅读平台听书系统 🌟 开篇：遇见未来，从“智听”开始 在这个快节奏的时代，你是否渴望在忙碌的间隙，找到一片属于自己的宁静角落？是否梦想着能随时随地，沉浸在知识的海洋，或是故事的奇幻世界里？今天，就让我带你一起探索“智听未来”——这一站式有声阅读平台听书系统，它正悄悄改变着我们的阅读方式，让未来触手可及！ 📚 第一站：海量资源，应有尽有 走进“智听
阅读更多...
在cscode中通过maven创建java项目

在cscode中通过maven创建java项目

在cscode中创建java项目 可以通过博客完成maven的导入 建立maven项目 使用快捷键 Ctrl + Shift + P 建立一个 Maven 项目 1 Ctrl + Shift + P 打开输入框2 输入 "> java create"3 选择 maven4 选择 No Archetype5 输入 域名6 输入项目名称7 建立一个文件目录存放项目，文件名一般为项目名8 确定
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2