excel表格_excel函数公式大全_execl从入门到精通
Excel-VBA正则表达式提取字符串数字案例和代码演示2024-03-23 00:11:31
内容提要：本文的Excel-VBA教程，主要分享的是正则表达式提取字符串数字的操作案例，通过案例来了解其中的VBA代码。
日常办公中，常会遇到这样一个难题：如何在惨不忍赌的数据中快速提取需要的字符串？如下图所示。要提取出第一列文本中转让的号码，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
本文配套的Excel正则表达式提取数字字符串课件，请到QQ群：365187805下载。
好了，介绍了这么多，今天的内容仅是正则表达式的九牛一毛。Excel正则表达式博大精深，绝非一两篇文章能够扫清的，这里仅仅做一些常见匹配模式罗列，有兴趣想系统学习VBA的伙伴，可以报读《零基础玩会VBA》课程。
为何要学VBA？依靠VBA，在效率上是一般同事的数倍甚至高一个量级。VBA不仅依旧是办公利器，而且是办公利器之王！
如何学好VBA？So easy！现在有老司机——小奇老师带你飞！将用20节精品视频课，带你玩转VBA！视频反复看+VIP群辅导答疑。
标签: Excel正则表达式函数的使用方法