Excel VBA正则表达式提取字符串数字实例教程
来源:原创 更新时间:2018-01-10 19:29:00 编辑:Legend 浏览:5583
日常办公中,常会遇到这样一个难题:如何在惨不忍赌的数据中快速提取需要的字符串?如下图所示。要提取出第一列文本中转让的号码,QQ号,联系电话等。一般情况下大家是如何操作呢?
原图:
提取效果:
在Excel中,单元格中简单的数据提取,我们可以使用LEFT、RIGHT、MID等函数来实现。
但如果单元格中既包含数字,又包含英文字母,且数字和字母个数不固定的情况下,如何处理呢?对于这种不规律的复杂字符,最好的办法就是使用VBA加上正则表达式来处理,简单迅速提取字符串数字,可大大提高工作效率。
一、什么是正则表达式?
正则表达式是一种特殊的字符串模式,用于匹配字符串排列的一套规则。我们可以用这个规则去匹配查找可以匹配的上的字符串(即单元格中任意你想要的信息)。简单来说,就是单元格中存在一个文本信息,这个信息中有一些我们需要的内容,也有很多我们不需要的内容,通过正则表达式,我们告诉Excel我们需要哪些内容,然后Excel帮助我们从文本中提取我们想要的内容。
例如:如何从一行杂乱的个人信息中匹配出身份证号码,因为身份证号码出现位置不固定,我们无法使用函数LEFT或者MID或者RIGHT来获取身份证号码,这边就可以考虑使用正则表达式,快速获取身份证号码。
在上面案例中的d以及出现的+、?都是什么意思呢?这些都是简单的正则表达式符号,了解这些字符在正则表达式中的暗语,你就可以快速处理文本数据了。
二、正则表达式的分类及常用符号
Excel中正则表达式概括分为3类:
第一类是字符类,例如d可以任意匹配一个数字字符,可以匹配0-9,[a-z]可以匹配任意一个小写字母;
第二类是限定符,可以控制字符出现的个数,例如手机号码一般是11位的数字,我们就可以使用[1-9]d{11}来表示,因为手机号码第一位不能为0,所以用[1-9]来匹配1-9中任意一个数字;
第三类是定位符,控制字符出现的位置。
此外,若想用好正则表达式,首先下面的几个常用字符必须要掌握:
通过上面各种符号的灵活组合就可以去匹配任何你想要的规则,搜索到你想要的文本信息。
三、正则表达式提取字符串数字案例分析
例如,从下表中的这段文本中提取出转让的手机号,QQ,以及联系电话。分析可知,转让的手机号码是由11位的数字组成,且号码之前是固定格式[转让],因此我们的正则表达式就可以写成[转让](d{11});QQ号类似分析,可得其正则表达式为QQ:(d*);联系电话的正则表达式为联系电话:(d{11})
明白了什么是正则表达式之后,下面就一起来看看本文案例的实现方法吧!
(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{11})",在txt中匹配联系电话
Case"Tel"
reg.Pattern ="联系电话:(d{11})"
'如果函数第二参数既不是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{11})";正则对象的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