Count digits before decimals and after decimals


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

Advertisements

M

Mike H

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

Ms-Exl-Learner

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

Ms-Exl-Learner

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

Advertisements

R

Ron Rosenfeld

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top