实例需求:数据保存在A列中,需要将其中数字部分(红色数字)乘以2(蓝色数字),其他部分不变。
图中示例数字出现在指定位置,并且数字位数是固定的,VBA处理起来就很简单。实际应用场景中,字符串可能是完全不规则的,就需要使用VBA的字符串函数逐个读取字符,并进行判断,提取数字之后,进行运算,然后再拼接字符串也可以实现,有些麻烦。
处理字符的任务,还是需要使用正则这个神器。
Sub RegExpDemo74_1()
Dim strTxt As String
Dim objRegEx As Object, objMatch As Object
Dim objMh As Object, c As Range
Set objRegEx = CreateObject("vbscript.regexp")
objRegEx.Global = True
objRegEx.Pattern = "(\d+)"
For Each c In Range([A2], Cells(Rows.Count, "A").End(xlUp))
strTxt = c.Value
Set objMatch = objRegEx.Execute(strTxt)
If objMatch.Count > 0 Then
For Each objMh In objMatch
With objMh
strTxt = VBA.Left(strTxt, .FirstIndex) & VBA.Replace(strTxt, .Value, .Value * 2, .FirstIndex + 1)
End With
Next
c.Offset(0, 1) = strTxt
End If
Next
Set objMh = Nothing
Set objMatch = Nothing
Set objRegEx = Nothing
End Sub
【代码解析】
第7行代码指定正则模式,\d+
代表一位或者多位数字。
第14行代码将数字替换为两倍的数字,先用Left函数获取捕获组之前的字符串,然后Replace替换时指定起始字符位置,这样可以避免被多次替换。例如:QLJ100FJP200XGN
,如果使用简单替换的话,第一次替换后结果为QLJ200FJP200XGN
,第二次替换时两个数字将都被替换为400,结果变为QLJ400FJP400XGN
,这显然是不对。
这个代码似乎也没有什么太复杂的,正则表达式也很普通,接下来要展示才是正题,使用JS的正则实现这个需求。
Sub RegExpDemo_JS()
Dim objJS As Object
Set objJS = CreateObject("MSScriptControl.ScriptControl")
objJS.Language = "javascript"
objJS.AddCode ("var r=/\d+/g;")
For Each c In Range([A2], Cells(Rows.Count, "A").End(xlUp))
objJS.AddCode ("var s = '" & c.Value & "';")
c.Offset(0, 1) = objJS.Eval("s.replace(r,function(p){return p*2})")
Next
Set objJS = Nothing
End Sub
【代码解析】
VBA调用JS的代码结构不再赘述了。
第5行代码指定JS正则模式,注意在JS中正则表达式使用的是/.../g
的形式。
第7行代码REPLACE
函数中符合正则模式的字符串,被替换为函数function(p)
的返回值,在这个JS自定义函数中可以轻松的实现数字乘以2的效果。
VBA调用JS的代码相对来说更简单些,但是需要大家具备JS的基本知识。VBA不支持这个回调函数的形式,既然是在Excel中实现,那么可以借助强大的公式来处理,和回调函数有与曲同工之处。
Sub RegExpDemo74_2()
Dim strTxt As String
Dim objRegEx As Object
Dim c As Range
Set objRegEx = CreateObject("vbscript.regexp")
objRegEx.Global = True
objRegEx.Pattern = "(\d+)"
For Each c In Range([A2], Cells(Rows.Count, "A").End(xlUp))
strTxt = c.Value
If objRegEx.test(strTxt) Then
c.Offset(0, 1) = Application.Evaluate(Chr(34) & objRegEx.Replace(strTxt, """&$1*2&""") & Chr(34))
End If
Next
Set objRegEx = Nothing
End Sub
【代码解析】
第10行代码测试单元格中的字符串是否包含指定正则模式。
第11行代码使用正则的REPLACE
函数,将捕获组替换为$1*2
,其中$1
代表捕获组的字符。
以第一个数据为例,正则替换后构建的公式为"QLJ"&100*2&"FJP"&110*2&"XGN"
,然后利用EVALUATE
计算公式的结果,并写入指定单元格。与工作表中单元格公式不同,作为EVALUATE
参数的公式可以没有等号。
这个实现方法比第一个VBA解决方案更简洁。
相关博文链接:
VBA之正则表达式(12)-- 格式调整
VBA之正则表达式(13)-- 字符串变换
VBA之正则表达式(14)-- 提取指定位数的数字
VBA之正则表达式(15)-- 提取数字求和
VBA之正则表达式(16)-- 提取非重复值
VBA之正则表达式(17)-- 提取多组数据(去除末尾字符)