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

 

[Norie]

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(vNums(lngNum))
    Next lngNum
End Function

 

[Mann750]

Hi Norie,

Thank you for your quick response. I tried the code but unfortunately it returns '0' if I have any text with the numbers. So if I just have

1
2
3

then I get '6' as the answer but if I have

red 1
green 2
blue 3

then I get '0'.

Also, the issue I have is that because my enquiries also have a number (Enquiry1) then the number (1) is included in the sum and I don't want it to be. If I could somehow look at the text from ":" onwards and only include those numbers which are in brackets and always have "m)" after them then I would be summing the right numbers.

 

[Norie]

What exactly are you trying to do?

If you are trying to sum the numbers in brackets from something like this then you'll need a bit more than code that splits on Chr(10).

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

 

[Mann750]

That's exactly what I am trying to do. I thought that maybe if I could just use Chr(10) that would solve the problem but when I ran your code and tried adjusting it to use some other delimiter it didn't work because of the other numbers in the text.

How could I adjust the code to only sum the numbers in the brackets with their "+" or "-" signs as part of the sum?

 

[Norie]

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

 

[Mann750]

That code works great! Thank you so much for being so quick

Could you explain to me what this line does in case I need to modify it?

SumNums = SumNums + Val(Mid(vNums(lngNum), InStr(vNums(lngNum), "(") + 1))

 

[Norie]

It finds the ( in and extracts everything after that which is then converted to a number using Val.

 

[Mann750]

That's great, should have used my head and worked that out for myself!

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?

 

[Norie]

You could possible use InStrRev to find the last occurence of (.

If things are going to get more complicated it's probably worth looking at using regular expressions to extract the number.