Excel 将单个单元格数字求和

首先自定义正则表达式函数

Function ExStr(Str As String, 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:
            ExStr = regex.Replace(Str, RepStr)
        Case 2:
            ExStr = regex.test(Str)
        Case 3:
            Dim matches As Object
            Set matches = regex.Execute(Str)
            For Each Match In matches
                ExStr = ExStr + CInt(Match.Value)
            Next
    End Select
End Function

加入宏 mudule中

ex:DALIAN CHINA: 12#LOS ANGELES CA: 1#DALIAN CHINA: 2#SAN DIEGO: 1#JINHE: 1#LOS ANGELES CA: 1#

formula: =ExStr($D3,"\d+",3)