想在A2单元格中提取A1中的数字求和,网上找了很多方法都不行,算出来结果不对,注意这个是带小数的很多数字,掺杂着中文和英文以及单位,包含换行,求大神帮忙,若是VB代码请讲清楚使用方法,谢谢!
ä½ è¿æ ·è®°å½æ°æ®æ¯èªå·±ç»èªå·±æ·»éº»ç¦ã
ä¸è¿ä¹è¿æ¯æå ¬å¼å¯ä»¥è®¡ç®çãå¦å¾ï¼
B1=SUM(IFERROR(--TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"åç","MW"),"MW",REPT(" ",199)),ROW($2:$9)*199-198,199)),0))
åæ¶æCtrl+Shift+Enterä¸é®è¾å ¥æ°ç»å ¬å¼
å ¬å¼ä¸çROW($2:$9)èèäºæ8个æ°åè¦æåï¼å¦ææ´å¤ï¼å 大æ°å9ã
VBA用空格和MW进行分割,代码如下:
Sub t()
s = Replace([A1].Value, " ", "")
Dim reg As Object
Dim mainMatch As Object 'Matchcollection
Dim subMatch As Object 'Match
Set reg = CreateObject("VbScript.regexp") '创建正则项目
With reg
.Global = True '匹配多次
.IgnoreCase = True '匹配大小写
.Pattern = "[\d.]+" '正则表达式,匹配选项
End With
Set mainMatch = reg.Execute(s) '执行语句
For Each subMatch In mainMatch '遍历
sm = sm + Val(subMatch)
Debug.Print subMatch
Next
Debug.Print sm
[A2] = sm
End Sub