sum numbers in carriage return text

[Mann750]

Hello

I have been looking through the forums to find an answer to my query and I seem to be close but don't know what I need to do next. I have found this post which sums numbers in a comma delimited text string and modified it to count using Chr(10) as the delimiter.

http://www.mrexcel.com/forum/excel-questions/505622-text-numbers-same-cell-sum-numbers.html

My code is now as follows:

Function SumNums(rngS As Range, Optional strDelim As String = "Chr(10)") As Double
    Dim vNums As Variant, lngNum As Long
    vNums = Split(rngS, strDelim)
    For lngNum = LBound(vNums) To UBound(vNums) Step 1
        SumNums = SumNums + Val(vNums(lngNum))
    Next lngNum
End Function

However, when I run it on a simple cell with "1, 2, 3" seperated by the carriage return after each number I get "123" as the sum and not "6"

Also the actual text is a little more complicated than this as I have text before each number in the format below:

Enquiry1: (+10m)
Enquiry2: (-15m)
Enquiry3: (+11m)

So the sum should be (+10-15+11)=6.

There will be varying lengths of text in one cell so the function will need to be as dynamic as possible!

Hope someone can help me

 

[Mann750]

Thank you SO much for your help Norie! I didn't realise there was a reverse of InStr and it works perfectly (so far). I shall do some more testing but for now thank you again You've been great

 

[Rick Rothstein]

You can try this.

Function SumNums(rngS As Range, Optional strDelim) As Double

    Dim vNums As Variant, lngNum As Long
    If IsMissing(strDelim) Then strDelim = Chr(10)
    
    vNums = Split(rngS, strDelim)
    For lngNum = LBound(vNums) To UBound(vNums) Step 1
        SumNums = SumNums + Val(Mid(vNums(lngNum), InStr(vNums(lngNum), "(") + 1))
    Next lngNum
End Function

I see later of that the OP has yet changed the criteria once again, but I developed this alternative to your function before I saw that he did that. This code does not care what the delimiter is, it just sums the values in parentheses (which, given the OP's latest change, would not work for him any more)...

Function SumNums(ByVal S As String) As Double
  Dim X As Long, Parts() As String
  Parts = Split(Replace(S, ")", "("), "(")
  For X = 1 To UBound(Parts) - 1 Step 2
    SumNums = SumNums + Val(Parts(X))
  Next
End Function

I am not sure how to modify this (yet) for the OP's latest request although it is beginning to look like every time we solve a problem for him, he up and changes the requirement.

 

[Rick Rothstein]

This may be (another) simple question but if the string had brackets around the Enquiry number, so for example "Enquiry(1)", how would I skip the first occurence of "(" and only sum the second occurence in each carriage return line?

There may be simpler code, but developing it depends on us know the full extent of the variations in your text. Will every number after the word "Enquiry" within a cell be in brackets or could some be in brackets and other not? Can the number after the word "Enquiry" have brackets around it in some cells and not have brackets around it in other cells?

 

[Rick Rothstein]

There may be simpler code, but developing it depends on us know the full extent of the variations in your text. Will every number after the word "Enquiry" within a cell be in brackets or could some be in brackets and other not? Can the number after the word "Enquiry" have brackets around it in some cells and not have brackets around it in other cells?

Actually, in thinking about it, this code may work...

Function SumNums(ByVal S As String, Optional Delim As String = vbLf) As Double
  Dim X As Long, Parts() As String
  Parts = Split(Replace(Replace(Range("J6"), "Enquiry(", ""), ")" & Delim, ""), "(")
  For X = 1 To UBound(Parts)
    SumNums = SumNums + Val(Parts(X))
  Next
End Function