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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|