Prev: One step paste of XLS file name in one of its own cells
Next: arquivo excel worksheet
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: Ms-Exl-Learner 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!

--------------------
(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?
From: Ms-Exl-Learner 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!

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