From: Elton Law on 27 May 2010 17:02 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?
From: Mike H on 27 May 2010 17:05 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?
From: MsExlLearner on 27 May 2010 17:09 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,LEN(MID(A1,FIND(".",A1)+1,255)))) OR =IF(A1="","",LEN(INT(A1))+IF(ISERROR(LEN(MID(A1,FIND(".",A1)+1,255))),0,LEN(MID(A1,FIND(".",A1)+1,255)))) Remember to Click Yes, if this post helps!  (MsExlLearner)  "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?
From: MsExlLearner on 27 May 2010 18:02 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!  (MsExlLearner)  "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?
From: Ron Rosenfeld on 27 May 2010 20:42 On Thu, 27 May 2010 14:02:31 0700, Elton Law <EltonLaw(a)discussions.microsoft.com> 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

Pages: 1 Prev: One step paste of XLS file name in one of its own cells Next: arquivo excel worksheet 