Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 173
Default 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   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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?

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 506
Default 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?

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 506
Default 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?



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
adding digits with moving decimals Loadmaster Excel Worksheet Functions 4 December 7th 09 07:56 AM
Adding digits with moving decimals Loadmaster Excel Worksheet Functions 2 November 28th 09 09:41 PM
I want to enter a figure &excel makes last two digits decimals Jude Excel Worksheet Functions 4 March 20th 07 07:10 PM
Format monetary amount to 9 digits with No decimals without rounding [email protected] Excel Worksheet Functions 4 December 21st 05 12:14 AM
significant digits for decimals Raj Excel Worksheet Functions 7 November 4th 05 02:26 AM


All times are GMT +1. The time now is 01:02 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"