Sum numbers in Cell

Alkemist

Board Regular
Joined
Nov 26, 2003
Messages
144
Hi Everyone,

Quick query for you...

Is it possible to sum the numbers in a cell which would have the following content?

12ml, 324sl, 1hl, 34gc

The numbers could very in length but there will always be 2 letters and each element in the cell would always be seperated by a comma. The number of elements in the cell could vary from anything from a cell containing nothing to a cell containing easily more than a dozen elements.

The sum of the number above would be 12+324+1+34 etc...

I don't believe there is a function in XL to do the above (could be wrong) so was thinking I might have to go the route of using macros.

What do you think?...Cheers...Alkemist
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

not sure if this is the shortest or the best formula, but it works for me

  A  B    
1 45 12kl 
2    13jl 
3    10ee 
4    1000 

Blad1

[Table-It] version 06 by Erik Van Geit
Code:
RANGE FORMULA (1st cell)
A1    {=SUM(IF(--(LEN(B1:B5)>2),--LEFT(B1:B5,LEN(B1:B5)-2)))}

{=formula}:
 enter formula without {}
 confirm with Control-Shift-Enter

[Table-It] version 06 by Erik Van Geit
kind regards,
Erik
 

Alkemist

Board Regular
Joined
Nov 26, 2003
Messages
144
Thanks for the reply...

How would you deal with a single cell that contained all of the data in your example?

So, in Cell A1 you had all of the following:

12kl, 13jl, 10ee

The reason this came about is that the cells containing the above started life as text for quick visual reference. As time has gone by it's become important to analyse the data within the cells.

Of course, this is further complicated by the fact that the list of cells I have are not uniform otehr than the data being seperated by a comma. The numbers change in digit length and the number of data elements in the cell varies as you go down the data.

I could have in cell A1: 12fd, 55,rf, 345lh, 3434,dl
then in cell A2: 33fd
cell A3: 3434ff, 333df, 33gf, 123fr, 555ff, 4th, 35yj, 13as, 99df, 33qu

etc...

Cheers Alkemist
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
what Excelversion are you using (is there "split" in the helpfiles) ?
check the commas in your example is this correct (see A1)

did the formula work ?

best regards,
Erik
 

Alkemist

Board Regular
Joined
Nov 26, 2003
Messages
144

ADVERTISEMENT

Hia...I've not had chance to try the formula because the work I need to try it on is at home, but, I think it may not work on my particular data set because it's combine into one cell rather than a list as your is (or does that not matter?)

I'm using XL 2002 SP3. I entered "split" in help and it didn't give me anything that would help with this problem.

I was looking at recording a macro that uses the Text to Columns feature but thought this was probably more complicated than required. I'm sure there is a more elegant solution ;o)...
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
I forgot to tell you to find SPLIT in the VBA-helpfiles.
You're on 2002, so that would work.

did you check the commas in your example ?

EDIT: why can't you test ?
I'm not at your home and can test using your example ...
 

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188

ADVERTISEMENT

*Warning* This is really ugly.
I did a work-around where I first extracted the numbers in one cell and then summed them in another. Like I said, it's ugly. You can probably nest these two, but I don't know quite how.
Master test.xls
ABCD
1abc1231236
Sheet1
 

infuse

Board Regular
Joined
Dec 7, 2005
Messages
126
erik.van.geit:

I have a question, I am learning Excel by trying to solve questions from this forum. I developed an equation which does the same, i GUESS. I know the original poster’s requirement is different. I just want to check my formula does work as the same way as yours. I checked myself and it did work for me. Thanks in advance for the time.
Code:
=SUM((LEFT(A1:A4,(LEN(A1:A4)-2)))*1)

Alchemist:

Sorry for the posting question in your thread

Infuse
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Infuse,
this is not a question but a nice ANSWER :)


1. works perfectly for me on the data showed in original question
2. needs to be array-entered
3. will return #VALUE
a. if there are cells with 2 or less characters
b. if there are cells with non-numerics like 2zab (2z is not numeric) (so does my formula, but I considered this as unimportant)

best regards,
Erik
 

infuse

Board Regular
Joined
Dec 7, 2005
Messages
126
erik.van.geit and Alkemist

I wrote this code. I checked and it did work. Can you guys check this and let me know. Especially i am looking for erik.van.geit's comments.
I am not a programmer or anything, i searched help files to write this.

Code:
Sub newone()
Dim txt As String
Dim nwtxt As Variant
For i = 1 To Range("A65536").End(xlUp).Row
   txt = Range("A" & i).Value
   nwtxt = Split(txt, ",")
     For j = 0 To UBound(nwtxt)
     Sm = Val(nwtxt(j))
     NwSm = NwSm + Sm
     Next j
Next i
MsgBox NwSm
End Sub
 

Forum statistics

Threads
1,136,621
Messages
5,676,852
Members
419,656
Latest member
lironprofit

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