Count digits before decimals and after decimals

  • Thread starter Thread starter Elton Law
  • Start date Start date
E

Elton Law

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?
 
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.
 
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!
 
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!
 
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?
Click to expand...

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