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?

Answer: Count digits before decimals and after decimals
 

To count the digits before and after the decimal point in a number:

1. Let's say your number is in cell A1. First, we need to find the position of the decimal point in the number. We can do this using the
   Code:

   ---

   FIND

   ---

   function. Enter the following formula in cell B1:
   
   Code:

   ---

   =FIND(".",A1)

   ---

   This will return the position of the decimal point in the number.
   
2. Now that we know the position of the decimal point, we can use the
   Code:

   ---

   LEFT

   ---

   and
   Code:

   ---

   RIGHT

   ---

   functions to count the digits before and after the decimal point, respectively. Enter the following formulas in cells C1 and D1:
   
   Code:

   ---

   =LEFT(A1,B1-1)

   ---

   (to count the digits before the decimal point)
   
   Code:

   ---

   =RIGHT(A1,LEN(A1)-B1)

   ---

   (to count the digits after the decimal point)
   The
   Code:

   ---

   LEFT

   ---

   function takes the leftmost characters of a string, and the
   Code:

   ---

   RIGHT

   ---

   function takes the rightmost characters of a string. We use the position of the decimal point that we found in step 1 to determine how many characters to take.
   
3. Finally, we can use the
   Code:

   ---

   LEN

   ---

   function to count the number of digits before and after the decimal point. Enter the following formulas in cells E1 and F1:
   
   Code:

   ---

   =LEN(C1)

   ---

   (to count the digits before the decimal point)
   
   Code:

   ---

   =LEN(D1)

   ---

   (to count the digits after the decimal point)
   The
   Code:

   ---

   LEN

   ---

   function simply counts the number of characters in a string.

That's it! You should now have the number of digits before and after the decimal point in your original number. You can drag these formulas down to apply them to other numbers in your worksheet.

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?

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?

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?

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