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

来源：原创更新时间：2018-01-10 19:29:00 编辑：Legend 浏览：5530

日常办公中，常会遇到这样一个难题：如何在惨不忍赌的数据中快速提取需要的字符串？如下图所示。要提取出第一列文本中转让的号码，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)

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)