Excel formula for count numbers of decimals

3
votes

I need to count the numbers of decimals places of a number.

The A1 cell value is:

    123456.78.

The formula in B1 is: 

    =LEN(MOD(A1,1))

The results of MOD(A1,1) is:

 0.78

I expected the LEN to be 4 (LEN(0.78)=4). The Excel formula calculates 17 because the forumula returns:

0.779999999998836

Should I try a different approach? For example looking for the separator char?

=LEN(A1)-FIND(".",A1)
2
excel
I'm finding this a little confusing - you want to count the number of decimal places in the cell value, which is 15 (plus a 0.). The displayed text is 4 characters long (0. and 2 decimal places). If you want it to be 2 decimal places then you could use =ROUND(A1,2) or =TEXT(A1,"0.00"). Or you could create a named range as =GET.CELL(53,A1) which will show the value as displayed and you can use a LEN function on that to return 4. – Darren Bartrup-Cook

2 Answers

2
votes

Try this:

=LEN(RIGHT(A1;LEN(A1)-FIND(",";A1)))

A better formula managing a non decimal entry and different decimal separators:

=IF(ISNUMBER(FIND(".";A1));LEN(A1)-FIND(".";A1);IF(ISNUMBER(FIND(",";A1));LEN(A1)-FIND(",";A1)))
1
votes

I see that the Len function is causing the math function to return the incorrect value for some reason (Len(Mod(123456.78, 1)) is returning 17 not 4, whereas Len(Mod(6.78,1) correctly returns 4).

You can add the TEXT function to your formula to change it to text, with a format of "General" to preserve the decimal precision, before calculating the length: LEN(TEXT(MOD(A1,1), "General")).

For those wanting to use this to calculate the number of decimal places without the leading "0.", simply subtract 2 from the result.