|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 27th, 2010, 10:02 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count digits before decimals and after decimals
Dear expert,
Would like to count the digits before decimals and after decimals. For example, 111975.35 should be 6 digits before decimals and 2 digits after decimals 12456.25 should be 5 and 2 2478.24 should be 4 and 2 248.37 should be 3 and 2 Using LEN does not help. Can you help please? 
|
|
#2
May 27th, 2010, 10:05 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count digits before decimals and after decimals
Hi,
Why doesn't LEN help. try these =LEN(INT(A1)) and for the decimal portion =IF(ISERROR(FIND(".",A1)),0,LEN(A1)-FIND(".",A1)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Elton Law" wrote: Dear expert, Would like to count the digits before decimals and after decimals. For example, 111975.35 should be 6 digits before decimals and 2 digits after decimals 12456.25 should be 5 and 2 2478.24 should be 4 and 2 248.37 should be 3 and 2 Using LEN does not help. Can you help please?
|
|
#3
May 27th, 2010, 10:09 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count digits before decimals and after decimals
After seeing Mike sir formula I realized that my formula needs some correction.
Corrected Formula:- =IF(A1="","",LEN(INT(A1))&"-"&IF(ISERROR(LEN(MID(A1,FIND(".",A1)+1,255))),0,LE N(MID(A1,FIND(".",A1)+1,255)))) OR =IF(A1="","",LEN(INT(A1))+IF(ISERROR(LEN(MID(A1,FI ND(".",A1)+1,255))),0,LEN(MID(A1,FIND(".",A1)+1,25 5)))) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Elton Law" wrote: Dear expert, Would like to count the digits before decimals and after decimals. For example, 111975.35 should be 6 digits before decimals and 2 digits after decimals 12456.25 should be 5 and 2 2478.24 should be 4 and 2 248.37 should be 3 and 2 Using LEN does not help. Can you help please?
|
|
#4
May 27th, 2010, 11:02 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count digits before decimals and after decimals
Try this in B1 cell
=IF(A1="","",LEN(INT(A1))&"-"&LEN(MID(A1,FIND(".",A1)+1,255))) If you want to add the length of Integer and Decimal then use the below formula in B1 cell =IF(A1="","",LEN(INT(A1))+LEN(MID(A1,FIND(".",A1)+ 1,255))) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Elton Law" wrote: Dear expert, Would like to count the digits before decimals and after decimals. For example, 111975.35 should be 6 digits before decimals and 2 digits after decimals 12456.25 should be 5 and 2 2478.24 should be 4 and 2 248.37 should be 3 and 2 Using LEN does not help. Can you help please?
|
|
#5
May 28th, 2010, 01:42 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count digits before decimals and after decimals
On Thu, 27 May 2010 14:02:31 -0700, Elton Law
wrote: Dear expert, Would like to count the digits before decimals and after decimals. For example, 111975.35 should be 6 digits before decimals and 2 digits after decimals 12456.25 should be 5 and 2 2478.24 should be 4 and 2 248.37 should be 3 and 2 Using LEN does not help. Can you help please? If you are interested only in significant digits, then before Decimal: =FIND(".",A1)-1 after Decimal: =LEN(A1)-FIND(".",A1) But if you are interested in also counting trailing or leading zeros, you will either need to enter the numbers as text, or use VBA to determine the format. --ron
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
