正则表达式在Excel中的应用

本篇信息来源常州分公司张志杰，有疑问联系他。欢迎关注，欢迎批评，欢迎转发！

正则表达式在文本处理中非常实用，遗憾的是Excel并没有支持正则表达式的内置函数。在我们的日常工作中经常需要用Excel来进行文字提取，虽然使用内置函数也可以实现，但公式十分复杂。本文将介绍通过VBA自定义函数，使得Excel支持正则表达式。
一、正则表达式(RegExp对象)
VBA中可以通过CreateObject("Vbscript.Regexp")语句来声明内置的正则表达式对象，该对象有3个属性和3个方法，分别是：
属性/方法
说明
Global属性
Global属性: True or False, 指明模式是匹配整个字符串中所有与之相符的地方还是只匹配第一次出现的地方。默认值是False(表示只匹配第一次出现的地方)
IgnoreCase
属性
True  or False, 指明模式匹配是否大小写敏感。默认是False(表示搜索为大小写敏感)
Pattern属性
设置或返回用于搜索的正则表达式
Execute方法
将正则表达式应用到字符串上并返回Matches集合
Replace方法
替换在正则表达式搜索中找到的文本
Test方法
对字符串执行正则表达式搜索，并返回一个布尔值 (True:匹配成功, False:匹配不成功)
二、元字符
VBA中的元字符与各家编程语言中的大体相同，此处不再赘述，但有几点特殊的地方需要说明。
1、VBA中正则表达式支持零宽度正（负）预测先行断言即(?=exp)或(?!exp)表达式，但不支持零宽度正（负）回顾后发断言即(?表达式。举例说明，有两个字符串”192.168.3.9:21”和”192.168.10.55:445”，需要提取端口为21的IP地址。造模式字符串为”\d+.\d+.\d+.\d+(?=:21)”， (?=:21)匹配:21但不捕获，如果调用Test方法，第一个会返回True，第二个返回False；如果调用Execute方法第一个返回”192.168.3.9”，第二个返回空。(?=exp)只能放在模式字符串的最后面，如果构造”(?=192.)\d+.\d+.\d+”这样的模式是非法的，应该使用(?Visual Basic，弹出VBA编辑窗口。

(3)    右键VBAProject，弹出菜单->插入->模块，插入一个新的模块，单击新插入的模块即可在右侧代码编辑框插入代码。

(4)    定义函数：Public Function Regex(src,pattern, Optional index As Integer = 0, Optional IgnoreCase As Boolean = False)。VBA通过Function关键字来定义函数，As关键字可以定义形参的类型，与其他语言类似，形参可以赋默认值。四个参数分别是源字符串、模式字符串、返回第几个结果、大小写敏感。
(5)    输入函数体内容如下图，并保存：

(6)    关闭所有Excel进程，将保存的xlam文件拷贝至C:\Program Files\MicrosoftOffice\Office15\Library目录（不固定，参考自己安装office的文件夹）
(7)    重新打开Excel，文件->选项，打开选项面板，单击加载项可以发现我们的加载宏文件已经在列表中了，单击信任中心->信任中心设置->加载项，把三个复选框的勾去掉（如果有的话），单击确定应用设置。

(8)    打开开发工具->加载项，勾选我们的加载宏，就可以在公式中的用户定义分类

下找到我们的自定义函数了。

(9)    提供已经写好的xlam文件，包括Regex、RegexReplace、IsMatch三个函数分别对应RegExp对象的三个方法。

初次投稿，分享一些工作中的一些思路，如有错误，欢迎各位领导同事批评指正，谢谢。