Adding numbers in String

GregDaw · Jun 10, 2003

I have the need to add the numbers in a string (i.e. 76432 = 22, 8121=12). This is used as a confiramtion code. The number resides in one cell and I would like a new column with the results. Any Ideas?

Aladin Akyurek · Jun 10, 2003

=SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0)

Joe4 · Jun 10, 2003

You can write a user defined function in VBA to do that quite easily. Here is the code:

Code:

Function SumNum(MyCell As Range) As Integer

    Dim MyLen, i As Integer
    
    MyLen = Len(MyCell)
    For i = 1 To MyLen
        SumNum = SumNum + Mid(MyCell, i, 1)
    Next i
    
End Function

Then, if the number is in cell A1, then in cell B1, enter the following:
=SUMNUM(A1)

NateO · Jun 10, 2003

Hello, if you want to do this in a worksheet, I think I would use Aladin's worksheet function over a vba udf.

But if you wanted to do this in VB for whatever reason, I might be tempted to stack a byte array and use it like:Function AddInp(myStrAsString)AsIntegerDim b()AsByte, zAsInteger, iAsInteger
b = myStrFor i =LBound(b)ToUBound(b)Step 2
z = z + Val(ChrW$(b(i)))Next
Erase b
AddInp = z
z =EmptyEndFunctionSub test()
MsgBox AddInp("76432")
MsgBox AddInp("764 32")
MsgBox AddInp("764 test 32")EndSub

I use the val statement to compensate for non numerical characters in your string. You could use it in a worksheet like:

Book3

A

B

C

D

1

123

6

2

1test2

3

Sheet1

But again, I might stick with the native functionality presented earlier unless you have text characters in your string.

GregDaw · Jun 11, 2003

Thank you all for the good imput. I had spent a good two hours on it before you all saved my sanity.

Greg

rrdonutz · Jun 20, 2003

Meant to post this last week, and wouldn't bother except that the Excel formula below handles just about everything. I haven't seen this formula used elsewhere, and this topic comes up from time to time (for example, see thread: http://www.mrexcel.com/board2/viewtopic.php?t=52283). I struggled with it myself, then came upon this formulation while working on another post (http://www.mrexcel.com/board2/viewtopic.php?p=239317#239317).

The non-array formula in cell B2 is:

=SUM((LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9},"")))*{1,2,3,4,5,6,7,8,9})

I also included a column for NateO's excellent UDF, which provides the same answers.

--Tom

MrExcel61703.xls