Adding digits in a single cell

[stevo]

Is it possible to total the number of digits in a single cell? e.g. if a
cell contains the number 23456 can you put a maths function in another
cell to display the total i.e 20? Many thanks.

 

[Bob Phillips]

Here you go

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[broro183]

Hi Stevo,

I've just adapted this from a user defined function (UDF) that sort
the digits in a cel
(http://www.excelforum.com/showthread.php?t=507991).

To use this UDF, press [alt + F11], [ctrl + R], go to Insert - Module
& paste the following in:


Function sum_digits(s As String) As Long
Application.Volatile 'This forces the function to update
Dim i As Long
For i = 1 To Len(s)
sum_digits = sum_digits + Mid(s, i, 1)
Next i
End Function

Now, if your # (eg 23456) that you want to sum is in A1, ente
"=sum_digits(A1)" into cell B2 & you should see 20 appear in cell B1.


For more tips/background & a link see:
http://www.excelforum.com/showthread.php?t=507919, &
http://www.excelforum.com/showthread.php?t=508265&highlight=Application.Volatile


hth,
Rob Brockett
NZ
Always learning & the best way to learn is to experience..