分享

「数据提取」正则表达式:如何在Excel中实现精准提取

 建发图书 2022-04-09

数据提取是Excel中常见的一类问题。

对于相对规律的数据,我们可以用函数(如用Left、Mid、Right)提取,或者用分列、快速填充等方式实现快速提取。

但有些数据或文本,根本没有规律可言,想从中提取目标文本不是一件容易的事。

文章图片1

例如图中B列单元格中的长文本,中间可能既有手机号码,又有座机号码,且这些电话号码的位置不固定。可以说,这是一份完全杂乱无规律的数据。

问题来了——像这样的无规律文本,怎么在Excel中实现精准提取呢?今天我们来介绍一个重要的知识点:正则表达式。

01什么是正则表达式?

正则表达式(Regular Expression),在代码中常简写为regex、regexp或RE,是计算机科学的一个概念,通常被用来检索、替换那些符合某个模式(或规则)的文本。

很多程序设计语言都支持利用正则表达式进行字符串操作,包括VBA。正则表达式表现为一串由提前定义好的特定字符、或由这些特定字符的组合所构成的“规则字符串”,如“\d+”就是一个正则表达式,表示任意多位的数字。

正则表达式中,一般包括两类字符,一种是正常的文本字符,另一种是元字符(就是指那些在正则表达式中具有特殊意义的专用字符)。比如,正则表达式“\babc”表示以“abc”开头的字符串,其中“abc”即为正常的文本字符,而“\b”则是一个元字符,代表“以特定字符开头的”。

以下为大家整理了常用的一些正则字符。

文章图片2

02利用正则表达式提取目标数据

回到本节开头的问题——从长文本中提取电话号码。

为了准确提取出电话号码,我们需要写出电话号码对应的正则表达式。由于号码中全是数字,因此可以使用元字符\d(代表数字)。而手机号与座机号又有不同,手机号是11位连续数字,而座机则是“4位区号-8位号码”的格式,因此提取手机号和座机号的正则表达式是不同的。

提取手机号:\d{11},表示连续的11位数字,也可以用:1\d{10},即1开头且后面还有10个数字。

提取座机号:\d{4}-\d+,表示“-”前有4位数字,“-”后面至少有1位数字。

同时提取手机号和座机号:\d{11}|\d{4}-\d+,“|”代表条件或,因此将前面两个正则表达式用“|”连起来,就可以同时提取手机号和座机号。

理解了正则表达式,接下来最关键的问题来了——正则表达式要写在哪里?如何利用正则表达式来提取目标文本呢?

在Excel中,我们需要借助VBA来实现,但是如果你安装了”Excel超能力“插件,则直接在正则输入框中输入正则表达式即可,往下看,会介绍到。

方式1——在VBA中应用正则表达式

第1步:ALT + F11,打开VBE,并新建一个模块;

第2步:在模块中编写如下代码:

Sub 提取电话()

Dim i, j As Long

Dim reg AsObject, PNums As Object

Set reg =CreateObject('VBScript.RegExp') '创建正则对象

reg.Global = True

reg.Pattern = '\d{11}|\d{4}-\d+' '正则模式,指定目标文本的正则表达式

For i = 3 ToCells(2, 2).End(4).Row

Set PNums =reg.Execute(Cells(i, 2).Value) '对单元格文本执行正则表达式

For j = 0 ToPNums.Count - 1

Cells(i,j + 3).Value = PNums(j).Value

Next j

Next i

End Sub

文章图片3

第3步:运行程序,一键即可完成自动提取。

代码中,Set reg =CreateObject('VBScript.RegExp')创建了正则对象,reg.Pattern = '\d{11}|\d{4}-\d+'指定了用于匹配的正则模式,即正则表达式。最后通过循环遍历,对各个单元格执行正则运算(reg.Execute(Cells(i, 2).Value)),并将结果依次填入目标单元格。

方式2——在Excel超能力插件中实现提取

在Excel内,想解决无规律文本的自动提取问题,你需要——第一,具备一定的VBA知识;第二,要熟练掌握正则表达式。但是,大部分朋友并没有学习过VBA,即使给到代码,一时半会也难以理解,更谈不上灵活应用了。

所以,仅仅掌握正则表达式是不够的。

因此,为了方便大家,我在”Excel超能力“插件中设计了一个专门用来完成正则提取的功能。不需要写VBA代码,只要在正则提取框内输入正则表达式,就可以自动完成提取,非常之方便!

下面以提取电话号码为例,来看操作步骤。

第1步:在正则提取框内输入正则表达式\d{11}|\d{4}-\d+,然后按回车键执行;

文章图片4

第2步:在弹出的对话框中选择要处理的区域,即B3:B6,确定;

第3步:在弹出菜单中选择提取结果的存放位置,选第1个单元格即可,这里为C3单元格,确定后,Excel超能力就会帮我们完成自动提取。

文章图片5

提取后的结果如下:

文章图片6

熟练掌握正则表达式后,你会觉得这个功能简直好用爆了。再举两个例子。

文章图片7

从文本中提取身份证号——由于身份证号后一位有可能是X,因此正则表达式可以写为:\d+X?,即本例长文本中至少有1位数字且结果是数字或者字母X的子文本。

文章图片8

从微信接龙中提取金额——上图中B列文本来自一份微信群接龙,由于每个人的填写习惯不同,有人使用的金额单位为万,也有人用小写的w,还有人用大写的W,总之都表示的“万”。

而且,金额数字除整数外,有的数字还包含小数。因此,用正则表达式“\d+(\.)?\d*[万wW]”表示这一类金额数字。

此外,由于有些人有两个账户暴雷,因此填写的金额为“数字1+数字2”的模式,为了将这类数字也提取出来,使用正则表达式:\d+\+\d{1,}

如果想同时将这两类金额信息提取到同一列,则可以用条件或“|”将两个正则表达式连起来,形成一个新的正则表达式:\d+(\.)?\d*[万wW]|\d+\+\d{1,}。

将上述正则表达式输入到Excel超能力的”正则提取'框内,按步骤选择数据区域和目标单元格,就能快速实现目标金额的提取。

● 超能力课堂:

一键提取中文、英文、数字、手机号、网址等信息

文章图片9

在Excel超能力的【文本处理】中还有一个【智能提取】功能,即使你不知道正则表达式怎么写,也可以完成一键提取。

这些提取目标包括——提取中文、提取英文、提取数字、提取手机号、提取电话号码、提取身份证号、提取邮政编码、提取Email地址、提取HTML标记、提取IP地址、提取URL。

安装了超能力的朋友,可以自行尝试。

文章图片10

▲ Office安装效果

文章图片11

▲ WPS安装效果

Excel超能力插件下载:


https://www./books/share/9a25fc0c-64e7-43c9-b2da-b6053577af88?# 《《Excel超能力》用户手册》

复制以上链接到电脑浏览器,内有下载地址。

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多