实例需求:数据保存在A列中,需要将其中的日期和金额分别提取至B列和C列。其中日期也两种不同格式,金额可能包含小数点、千分符和货币标识。
示例代码如下。
Sub RegExp_Date_Num()
Dim Res()
Dim objRegEx As Object
Dim objMH As Object
Dim j As Integer
Set objRegEx = CreateObject("vbscript.regexp")
objRegEx.Pattern = "(\d{4}-\d{2}-\d{2}|\d{4}\.\d{2}\.\d{2}).*?(([A-Z]{3})*\d+[\d.,]*元)"
objRegEx.Global = True
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
form = Cells(i, "A")
Set objMH = objRegEx.Execute(form)
If objMH.Count > 0 Then
Cells(i, 2) = CStr(objMH(0).submatches(0))
Cells(i, 3) = CStr(objMH(0).submatches(1))
End If
Next
Set objRegEx = Nothing
Set objMH = Nothing
End Sub
【代码解析】
第6行代码使用后期绑定创建正则对象。
第7行代码指定正则匹配字符串,
匹配模式 | 含义 |
---|---|
(\d{4}-\d{2}-\d{2}|\d{4}.\d{2}.\d{2}) | 用于匹配两种日期格式,竖线代表“或者”,最外侧的圆括号代表提取匹配的字符组 |
.*? | 匹配日期和金额之间的字符 |
(([A-Z]{3})*\d+[\d.,]*元) | 匹配金额部分,圆括号含义与上面相同 |
([A-Z]{3})* | 匹配三个字母的货币简称,也可以没有 |
\d+[\d.,]*元 | 一个或者多个数字、小数点、千分位 |
如果直接使用[\d.,]+
匹配金额中的数字,则会匹配到单个小数点或者逗号,例如数据为:旧的一年结束,元旦将要来临
,此模式可以将匹配到,元
。由此可见组织一个严谨的正则表达式需要考虑很多情况。
另外需要注意日期的匹配,粗略看,两种日期格式只是分隔符不同,可以合并简化正则表达式(\d{4}[-\.]\d{2}[-\.]\d{2})
,但是这样将会匹配到2019-12.25
这样的非标准日期格式。
第8行代码设置为全局搜索模式。
第9行代码第16行代码循环处理工作表中的数据。
第11行代码执行正则匹配,返回结果为MatchCollection
集合对象。
第13行和第14行代码分别将日期和金额写入工作表中。
相关博文链接:
VBA之正则表达式(1)-- 基础篇
VBA之正则表达式(2)-- 批量修改公式
VBA之正则表达式(3)-- 特殊公式计算
VBA之正则表达式(4)-- 提取日期和金额
VBA之正则表达式(5)-- 中文字符
VBA之正则表达式(6)-- 设置音标格式
VBA之正则表达式(7)-- 乾坤大挪移(数据整理)
VBA之正则表达式(8)-- 重复字符分组
VBA之正则表达式(9)-- 添加千分位(1/3)
VBA之正则表达式(10)-- 添加千分位(2/3)
VBA之正则表达式(11)-- 添加千分位(3/3)