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.
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.
Here you go
=SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"stevo" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> stevo
> ------------------------------------------------------------------------
> stevo's Profile:
http://www.excelforum.com/member.php...o&userid=10737
> View this thread: http://www.excelforum.com/showthread...hreadid=512178
>
edit: WOW! Thanks Bob, that's awesome - I need to learn more about the possibilities of "sumproduct"!
Stevo, here's another option - but personally, now that I've seen it, I'd use Bob's suggestion.
I've just adapted this from a user defined function (UDF) that sorts the digits in a cell (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, enter "=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...ation.Volatile
hth,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...
Last edited by broro183; 02-14-2006 at 08:03 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks