Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding digits in a single cell
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding digits in a single cell
Here you go
=SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "stevo" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding digits in a single cell
Hi Stevo, 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...ion.Vola tile hth, Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=512178 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Color a single digit in a mult-digit number cell | Excel Discussion (Misc queries) | |||
Formula and Data Entry in a Single Cell | Excel Discussion (Misc queries) | |||
Adding a Single Cell total from Seperate sheets | Excel Worksheet Functions | |||
adding zero's to a text cell | Excel Worksheet Functions | |||
adding a formula in a cell but when cell = 0 cell is blank | Excel Worksheet Functions |