将下面混合的数据直接计算出结果:
分析:这个数据其实很有规律
1、“+”加号分割,4段字符串
2、"["左方括号前面是数值,后面是字符
所以可以考虑使用公式提取数值直接运算。
公式法:
=SUM(--LEFT(TRIM(MID(SUBSTITUTE(A2,"+",REPT(" ",LEN(A2))),(ROW(1:4)-1)*LEN(A2)+1,LEN(A2))),FIND("[",TRIM(MID(SUBSTITUTE(A2,"+",REPT(" ",LEN(A2))),(ROW(1:4)-1)*LEN(A2)+1,LEN(A2))))-1))
我们来看看这个公式的组成:
这个公式最核心的部分:就是上图标颜色的部分,功能是:
将原来的字符串从加号(“+”)处分成4段。
这段公式,我们在前面讲过,稍有不同的地方是,MID函数的截取位置参数,也就是第二参数:
(ROW(1:4)-1)*LEN(A2)+1
前面讲的这个参数我们使用的单个行号或列号参与运算,
在这个例子里面,为了一次性计算4个值的和,我们采用了数组参与运算。
用ROW(1:4)构造了一个这样的数组
直接一次性处理4段字符串。
我们用TRIM代替这段核心代码,公式就简化成这个样子:
记住这里的TRIM是4个字符串组成的数组,并不是单个字符
SUM----是求和,求什么和?
求的是--用LEFT从TRIM中截取出来的数值的和(“--”用来将字符转换成数值)
LEFT截取的长度是FIND“["的位置减去1。
以上是公式法的过程分析。
正则法:
上一篇我们讲过用自定义正则函数提取数字或字符
正好这里可以用到。
不过要稍作改动:
为了求和,也需要将字符串转换为数值,然后相加
用Val函数转换成数值,加上找到的符合条件的值。
附上代码,方便大家做测试
Function ExAdd(Str As Range, Parttern As String, ActionID As Integer, Optional RepStr As String = "")
Dim regex As Object
Set regex = CreateObject("vbscript.regexp")
With regex
.Global = True
.IgnoreCase = True
.MultiLine = True
.Pattern = Parttern
End With
Select Case ActionID
Case 1:
ExAdd = regex.Replace(Str, RepStr)
Case 2:
ExAdd = regex.test(Str)
Case 3:
Dim matches As Object
Set matches = regex.Execute(Str)
For Each Match In matches
ExAdd = Val(ExAdd) + Match.Value '用Val转换为数值并相加
'ExStr = ExStr & Match.Value 用&链接字符串
Next
End Select
End Function
当然还要用到正则表达式
这个表达式就是提取浮点数值用的表达式
这两种方法大家都可以试一试
公式法,别忘记按三键CTRL+SHIFT+ENTER。
正则法,别忘记保存为启用宏的工作簿再使用。
领取专属 10元无门槛券
私享最新 技术干货