# sum numbers in carriage return text

#### Mann750

##### Board Regular
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"

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
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
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
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

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
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

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
It finds the ( in and extracts everything after that which is then converted to a number using Val.

#### Mann750

##### Board Regular
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
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.

Replies
2
Views
393
Replies
13
Views
193
Replies
3
Views
150
Replies
1
Views
138
Replies
4
Views
147

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.

### Which adblocker are you using?

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

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