Excel乱序中提取信息的简便招法

日常办公中,常会遇到这样一个难题:如何在惨不忍赌的数据中快速提取需要的字符串?如下图所示。要提取出第一列文本中转让的号码,QQ号,联系电话等。一般情况下大家是如何操作呢?

原图:

提取效果:

在Excel中,单元格中简单的数据提取,我们可以使用LEFT、RIGHT、MID等函数来实现。

但如果单元格中既包含数字,又包含英文字母,且数字和字母个数不固定的情况下,如何处理呢?对于这种不规律的复杂字符,最好的办法就是使用VBA加上正则表达式来处理,简单迅速,可大大提高工作效率。

一、什么是正则表达式?

正则表达式是一种特殊的字符串模式,用于匹配字符串排列的一套规则。我们可以用这个规则去匹配查找可以匹配的上的字符串(即单元格中任意你想要的信息)。简单来说,就是单元格中存在一个文本信息,这个信息中有一些我们需要的内容,也有很多我们不需要的内容,通过正则表达式,我们告诉Excel我们需要哪些内容,然后Excel帮助我们从文本中提取我们想要的内容。

例如:如何从一行杂乱的个人信息中匹配出身份证号码,因为身份证号码出现位置不固定,我们无法使用函数LEFT或者MID或者RIGHT来获取身份证号码,这边就可以考虑使用正则表达式,快速获取身份证号码。

在上面案例中的\d以及出现的+、?都是什么意思呢?这些都是简单的正则表达式符号,了解这些字符在正则表达式中的暗语,你就可以快速处理文本数据了。

二、正则表达式的分类及常用符号

Excel中正则表达式概括分为3类:

第一类是字符类,例如\d可以任意匹配一个数字字符,可以匹配0-9,[a-z]可以匹配任意一个小写字母;

第二类是限定符,可以控制字符出现的个数,例如手机号码一般是11位的数字,我们就可以使用[1-9]\d来表示,因为手机号码第一位不能为0,所以用[1-9]来匹配1-9中任意一个数字;

第三类是定位符,控制字符出现的位置。

此外,若想用好正则表达式,首先下面的几个常用字符必须要掌握:

通过上面各种符号的灵活组合就可以去匹配任何你想要的规则,搜索到你想要的文本信息。

三、正则表达式案例分析

例如,从下表中的这段文本中提取出转让的手机号,QQ,以及联系电话。分析可知,转让的手机号码是由11位的数字组成,且号码之前是固定格式[转让],因此我们的正则表达式就可以写成\[转让\](\d);QQ号类似分析,可得其正则表达式为QQ:(\d*);联系电话的正则表达式为联系电话:(\d)

明白了什么是正则表达式之后,下面就一起来看看本文案例的实现方法吧!

(1) 按ALT+F11(若右键-查看代码)弹出VBE窗口,然后新建模块,如下图:(切记需要引用Microsoft VBScript Regular Expressions,在VBE窗口中,选择工具—>引用)

(2) 粘贴的代码。

(3)保存完代码之后返回到表格中,在单元格输入我们刚才写的自定义函数=GetNumber(B3,”QQ”),如下图:

代码解析

函数包含2个参数,第一个参数txt就是我们在EXCEL单元格中需要处理的文本,第二个参数值如果是QQ,那么函数的返回值就是提取的QQ号码;如果第二个参数值是Tel,那么函数的返回值就是联系电话,否则不返回任何信息。

Function GetNumber(txt As String, searchtype As String) AsString

'定义正则对象

Dim reg As NewRegExp

'设置正则对象的Pattern属性,值为正则表达式字符串

Select Casesearchtype

'如果函数第二参数值为QQ,则设置正则对象的Pattern属性为"QQ:(\d*)",在txt中匹配QQ号码

Case"QQ"

reg.Pattern ="QQ:(\d*)"

'如果函数第二参数值为Tel,则设置正则对象的Pattern属性为"联系电话:(\d)",在txt中匹配联系电话

Case"Tel"

reg.Pattern ="联系电话:(\d)"

'如果函数第二参数既不是QQ,也不是Tel,则退出函数

Case Else

Exit Function

End Select

Dim mh AsMatchCollection, m As Match

'在文本中执行正则表达式搜索,返回值为MatchCollection对象

Set mh =reg.Execute(txt)

For Each m In mh

'm为Match对象,获取每一个Match对象结果中的子项集合SubMatches

GetNumber =m.SubMatches.Item(0)

Next

End Function

注:其中reg.Pattern= "QQ:(\d*)"以及reg.Pattern= "联系电话:(\d)";正则对象的Pattern属性值即为正则表达式,

在上面的案例中,出现很多对象,例如RegExp,MatchCollection这些是什么呢?我们有必要学习下VBA中的正则对象。

四、VBA中正则对象的常用属性和方法

在VBA中,要使用正则表达式,那么首先是需要一个正则对象,其次告诉正则对象我们的正则表达式是什么,然后正则对象才开始根据我们设置的正则表达式开始搜索文本,是否有匹配内容,如果存在匹配,我们可以将其返回,这也就是我们使用正则表达式的目的了。

那么,根据上面的一个逻辑,也就是定义正则对象——》设置正则表达式——》搜索文本——》是否存在匹配 我们来学习下VBA中的正则对象吧。

Dim reg As New RegExp 通过这句话,我们就定义了一个正则对象reg;

有了正则对象之后,我们又如何来告诉这个正则对象,正则表达式是什么呢?正则对象有一个属性是Pattern,这个属性的值就是我们的正则表达式,是一个字符串。例如上面案例中的reg.Pattern= "QQ:(\d*)" 在这个案例中,QQ:(\d*)这个就是一个正则表达式,其中因为我们需要的只有数字串,并不需要QQ:这几个字符,所以我们可以将(\d*) 用括号括起来。

接下来,就是正则对象开始工作,搜索文本啦, 也就是上面案例中的reg.Execute(搜索的文本) 其中这句话返回的是一个匹配集合,也是一个对象MatchCollection,对这个集合进行循环,取出每一个匹配对象也就是Match对象;而Match对象中的SubMatches.Item(0)就是我们需要提取的内容。也就是下面这段代码:

Dim mh AsMatchCollection, m As Match

'在文本中执行正则表达式搜索,返回值为MatchCollection对象

Set mh = reg.Execute(txt)

For Each m In mh

'm为Match对象,获取每一个Match对象结果中的子项集合SubMatches

GetNumber = m.SubMatches.Item(0)

Next

好了,介绍了这么多,今天的内容仅是正则表达式的九牛一毛。Excel正则表达式博大精深,绝非一两篇文章能够扫清的,这里仅仅做一些常见匹配模式罗列,有兴趣想系统学习VBA的伙伴,可以报读《零基础晚会VBA》课程。

为何要学VBA?

依靠VBA,在效率上是一般同事的数倍甚至高一个量级。

VBA不仅依旧是办公利器,而且是办公利器之王!

如何学好VBA?

So easy!

现在有老司机——小奇老师带你飞!

将用20节精品视频课,带你玩转VBA!

视频反复看+VIP群辅导答疑

  • 发表于:
  • 原文链接:http://kuaibao.qq.com/s/20180105G01TIU00?refer=cp_1026

相关快讯

扫码关注云+社区