Excel混合数据直接计算公式法与正则法

将下面混合的数据直接计算出结果：

分析：这个数据其实很有规律

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。

正则法，别忘记保存为启用宏的工作簿再使用。