# Sum numbers in Cell

#### Alkemist

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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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

[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

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

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

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

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

*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

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

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

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

Replies
10
Views
928
Replies
4
Views
298
Replies
4
Views
122
Replies
3
Views
308
Replies
8
Views
576

1,217,383
Messages
6,136,264
Members
450,001
Latest member
KWeekley08

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

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