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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,281
Office Version
  1. 365
Platform
  1. 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
76,281
Office Version
  1. 365
Platform
  1. 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

ADVERTISEMENT

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
76,281
Office Version
  1. 365
Platform
  1. 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

ADVERTISEMENT

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
76,281
Office Version
  1. 365
Platform
  1. 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
76,281
Office Version
  1. 365
Platform
  1. 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,129,574
Messages
5,637,163
Members
416,959
Latest member
Mohzein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top