Sum numbers in Cell

[Alkemist]

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

 

[erik.van.geit]

Hi, guys,

here is a function - as mentioned using SPLIT - which will do the job
(couldn't find a good name for it but that's your problem )
trying out the most stupid inputs, I couldn't make it bug

  A                                  B        C         
1 data                               sum cell sum range 
2 12345, 4000, er12                  163      173       
3                                    0                  
4 12ert,,moststupidinput,,,,,,,,10aa 10                 

Blad1
[Table-It] version 06 by Erik Van Geit

RANGE FORMULA (1st cell)
B2:B4 =sumleft(A2,2)
C2    =sumleft(A2:A4,2)

[Table-It] version 06 by Erik Van Geit

Option Explicit

Function sumleft(sumRng As Range, cutRight As Integer)
'Erik Van Geit
'060925

Dim c As Range
Dim arr As Variant
Dim i As Integer
Dim sumItems As Long
Dim it As String


    For Each c In sumRng
    arr = Split(c, ",")
    
        For i = 0 To UBound(arr)
        it = ""
        On Error Resume Next
        it = Left(arr(i), Len(arr(i)) - cutRight)
        If IsNumeric(it) Then sumItems = sumItems + it
        Next i
        
    Next c
    
sumleft = sumItems
End Function

If you change it's name, think to replace all occurences
Function sumleft(sumRng As Range, cutRight As Integer)
sumleft = sumItems

best regards,
Erik

 

[erik.van.geit]

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.

Hi, infuse,

this last sentence could be mine

put on top of your code
Option Explicit
and see what happens

also your code would not return the same result as mine: see the original question (cut off the last two characters) and the table I posted

best regards,
Erik

 

[Krishnakumar]

Hi,

Book1
	A	B	C	D
1	12ml, 324sl, 1hl, 34gc	371
2	12fd, 55,rf, 345lh, 3434,dl	3846
3	33fd	33
4	3434ff, 333df, 33gf, 123fr, 555ff, 4th, 35yj, 13as, 99df, 33qu	4662
Sheet2

Formula in B1:

=EVAL(SUBSTITUTE(REGEX.SUBSTITUTE(A1,"[a-zA-Z\,]")," ","+"))

EVAL and REGEX.SUBSTITUTE are part of MOREFUNC Add-In