sum numbers in carriage return text

Mann750

Board Regular
Joined
Dec 16, 2009
Messages
72
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:

Code:
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" :confused:

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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,530
Office Version
365
Platform
Windows
Try this.
Code:
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

Board Regular
Joined
Dec 16, 2009
Messages
72
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,530
Office Version
365
Platform
Windows
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

Board Regular
Joined
Dec 16, 2009
Messages
72
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,530
Office Version
365
Platform
Windows
You can try this.
Code:
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

Board Regular
Joined
Dec 16, 2009
Messages
72
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?

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,530
Office Version
365
Platform
Windows
It finds the ( in and extracts everything after that which is then converted to a number using Val.
 

Mann750

Board Regular
Joined
Dec 16, 2009
Messages
72
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,530
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,178
Messages
5,442,847
Members
405,202
Latest member
Mira_Xcel

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top