返回首页
当前位置: 主页 > Excel教程 > Excel VBA教程 >

Excel VBA正则表达式提取字符串数字实例教程

时间:2018-01-10 19:29来源:Office教程学习网 www.office68.com编辑:麦田守望者

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

   原图:
正则表达式提取数字 
 
  提取效果:
正则表达式提取字符串
 
  在Excel中,单元格中简单的数据提取,我们可以使用LEFT、RIGHT、MID等函数来实现。
 
  但如果单元格中既包含数字,又包含英文字母,且数字和字母个数不固定的情况下,如何处理呢?对于这种不规律的复杂字符,最好的办法就是使用VBA加上正则表达式来处理,简单迅速提取字符串数字,可大大提高工作效率。 
 
一、什么是正则表达式?
 
  正则表达式是一种特殊的字符串模式,用于匹配字符串排列的一套规则。我们可以用这个规则去匹配查找可以匹配的上的字符串(即单元格中任意你想要的信息)。简单来说,就是单元格中存在一个文本信息,这个信息中有一些我们需要的内容,也有很多我们不需要的内容,通过正则表达式,我们告诉Excel我们需要哪些内容,然后Excel帮助我们从文本中提取我们想要的内容。
 
  例如:如何从一行杂乱的个人信息中匹配出身份证号码,因为身份证号码出现位置不固定,我们无法使用函数LEFT或者MID或者RIGHT来获取身份证号码,这边就可以考虑使用正则表达式,快速获取身份证号码。
 
<span class=keyword><a href=http://www.ittribalwo.com/article/list_1.html target=_blank>excel教程<a></span>
 
  在上面案例中的\d以及出现的+、?都是什么意思呢?这些都是简单的正则表达式符号,了解这些字符在正则表达式中的暗语,你就可以快速处理文本数据了。
 
  二、正则表达式的分类及常用符号
 
  Excel中正则表达式概括分为3类:
 
  第一类是字符类,例如\d可以任意匹配一个数字字符,可以匹配0-9,[a-z]可以匹配任意一个小写字母;
 
  第二类是限定符,可以控制字符出现的个数,例如手机号码一般是11位的数字,我们就可以使用[1-9]\d{11}来表示,因为手机号码第一位不能为0,所以用[1-9]来匹配1-9中任意一个数字;
 
  第三类是定位符,控制字符出现的位置。
 
  此外,若想用好正则表达式,首先下面的几个常用字符必须要掌握:
VBA教程  
 
  通过上面各种符号的灵活组合就可以去匹配任何你想要的规则,搜索到你想要的文本信息。
 
  三、正则表达式提取字符串数字案例分析
 
  例如,从下表中的这段文本中提取出转让的手机号,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
顶一下
(1)
100%
踩一下
(0)
0%
标签(Tag):excel excel2013 vlookup函数 vlookup函数na错误
------分隔线----------------------------
------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
验证码:点击我更换图片
猜你感兴趣