Excel: Sum numbers inside a text block in a cell [

问题:

Lets say there is some text in a cell that reads

"this is a block of text (.7) and in this block of text (1.2) there are numbers (2.5) and these numbers need to be added together (.4)"

The answer to the sum of all these numbers would be .7+1.2+2.5+.4= 4.8

My question is, is there a way that I can have excel add all the numbers together from a block of text and just output the answer? It will always be the sum of the numbers and the numbers will always be positive. The amount of numbers will vary, it could be 2 it could be 15, could be anything.

What I have tried so far: I've tried "=sum" and highlighting the entire cell which always gives the answer "0"

回答1:

Try the following User Defined Function:

Public Function Addum(rng As Range) As Double
    Dim s As String, L As Long, temp As String
    Dim CH As String
    s = rng.Value
    L = Len(s)
    For i = 1 To L
        CH = Mid(s, i, 1)
        If CH Like "[0-9]" Or CH = "." Then
            temp = temp & CH
        Else
            temp = temp & " "
        End If
    Next i

    temp = Application.WorksheetFunction.Trim(temp)
    arr = Split(temp, " ")
    For Each a In arr
        Addum = Addum + CDbl(a)
    Next a
End Function

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

EDIT#1:

The original code tries to convert a standalone period into a number. Replace the original UDF with this version:

Public Function Addum(rng As Range) As Double
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'       VERSION #2
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim s As String, L As Long, temp As String
    Dim CH As String
    s = rng.Value
    L = Len(s)
    For i = 1 To L
        CH = Mid(s, i, 1)
        If CH Like "[0-9]" Or CH = "." Then
            temp = temp & CH
        Else
            temp = temp & " "
        End If
    Next i

    temp = Application.WorksheetFunction.Trim(temp)
    arr = Split(temp, " ")
    For Each a In arr
        If IsNumeric(a) Then
            Addum = Addum + CDbl(a)
        End If
    Next a
End Function

EDIT#2:

This version (VERSION 3) will only process numbers encapsulated in parens:

Public Function Addum(rng As Range) As Double
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'       VERSION #3
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim s As String, L As Long, temp As String
    Dim CH As String
    s = rng.Value
    L = Len(s)
    For i = 1 To L
        CH = Mid(s, i, 1)
        If CH Like "[0-9]" Or CH = "." Or CH = "(" Or CH = ")" Then
            temp = temp & CH
        Else
            temp = temp & " "
        End If
    Next i

    temp = Application.WorksheetFunction.Trim(temp)
    arr = Split(temp, " ")
    For Each a In arr
        If Left(a, 1) = "(" Then
            a = Mid(a, 2, Len(a) - 2)
            If IsNumeric(a) Then
                Addum = Addum + CDbl(a)
            End If
        End If
    Next a
End Function

回答2:

Here is a UDF using Regular Expressions which will add only those values that are within parentheses:

Option Explicit
Function sumNumsInParenth(S As String) As Double
    Dim RE As Object, MC As Object, M As Object
    Dim dSum As Double

Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .Pattern = "\((\d*(?:\.\d+)?)\)"
    If .test(S) = True Then
        Set MC = .Execute(S)
        For Each M In MC
            dSum = dSum + M.submatches(0)
        Next M
    End If
End With

sumNumsInParenth = dSum

End Function

Explanation of the Regex pattern

capture floating point numbers within parentheses, integer portion optional

\((\d*(?:\.\d+)?)\)

Options: Case insensitive; ^$ match at line breaks

• Match the opening parenthesis character \(
• Match the regex below and capture its match into backreference number 1 (\d*(?:\.\d+)?)
  • Match a single character that is a “digit” \d*
    • Between zero and unlimited times, as many times as possible, giving back as needed (greedy) *
  • Match the regular expression below (?:\.\d+)?
    • Between zero and one times, as many times as possible, giving back as needed (greedy) ?
    • Match the character “.” literally \.
    • Match a single character that is a “digit” \d+
      • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +
• Match the closing parenthesis character \)

Created with RegexBuddy

回答3:

This array formula will do it:

=SUM(IF(ISNUMBER(--TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"(",REPT(" ",99)),")",REPT(" ",99)),(ROW(1:100)-1)*99+1,99))),--TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"(",REPT(" ",99)),")",REPT(" ",99)),(ROW(1:100)-1)*99+1,99))))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.