# Count number of decimal places

• Hello,

I want to show (in a cell) how much decimals there are after a comma.

So, i hope you understand me...

Thanks!

• Re: Count after comma...

Oeste, welcome to Ozgrid.

Using this formula for a value in A1 you must format the cell to text or general and NOT number - A1 that is!

=IF(ISERROR(FIND(",",A1)),0,LEN(A1)-FIND(",",A1))

If the data is in number format then I don't know the answer

________________________________________

Good Luck!

Ray :nana:

• Re: Count after comma...

Thanks!

My cell was allready a "General"

Sorry for my bad English :d

• Re: Count after comma...

This should work for numbers too

(I have used decimal point as opposed to European "comma" ok so replace if necessary)

sample data A1:A5

15.9845
17.256
15.11
15.2
15

fomula in B1 copied down

=IF(A1=INT(A1),0,LEN(MID(A1-INT(A1),FIND(".",A1,1),LEN(A1)-FIND(".",A1,1))))

result

4
3
2
1
0

Any good

• Re: Count number of decimal places

Thanks!
This is better :d

But Now, I want to tally

I have a Number, 4 for instance.
Then I want in cell N7: llll (4xL)

When a number is 9
I want in cell N7: lllll llll

When the number is 15
I want in a N7: lllll lllll lllll

You've helped me already a lot

• Re: Count number of decimal places

OK

Let's say A1 is 15.9454

B1 is result of previous formula giving value of 4

In C1 type =REPT("l",B1)

result would be llll

This what you meant ?

• Re: Count number of decimal places

Yes, this is what i want.
But now i want to group the "l" per 5.

Something like this:

lllll lllll lllll l

• Re: Count number of decimal places

hello all (my first post here)

alternative way (works with number formatted cells and with all decimal separator) up to 5 decimal places of the input number. you can change the round to parameter for more.

=MAX(0;LEN(ROUND(A1-INT(A1);5))-2)

sample data:
4.13228
4.1389
4.476
4.14
3.78
0.58
-0.22
result:
5.00
4.00
3.00
2.00
2.00
2.00
2.00
sample:
4.55228
4.5589
4.896
4.56
4.2
1
0.2
result:
5.00
4.00
3.00
2.00
1.00
0.00
1.00

• Re: Count number of decimal places

Hi everybody, is my first post

How could excel show or find in a singular cell, the maximum value of decimal places for a range of numbers?

• Re: Count number of decimal places

Hello and welcome to Ozgrid.

Your question would be clearer if you provided examples of what you mean.

The MAX function returns the largest value.
The LEN function returns the length of a string - includes the decimal if the string is not a whole number.

Here is my guess:
Say the range A1:A5 contain decimal numbers

We could use:
[f]
{=MAX(LEN(A1:A5))-2}
[/f]

Note: the above is an array formula and must be committed using the key combination of Ctrl + Shift + Enter

To determine the number of characters to the right of the decimal.
Does that work for you?

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• Re: Count after comma...

This should work for numbers too

(I have used decimal point as opposed to European "comma" ok so replace if necessary)

sample data A1:A5

15.9845
17.256
15.11
15.2
15

fomula in B1 copied down

=IF(A1=INT(A1),0,LEN(MID(A1-INT(A1),FIND(".",A1,1),LEN(A1)-FIND(".",A1,1))))

result

4
3
2
1
0

Hi

I needed something similar to this formula above you posted for another user, however I need it to be able to count the trailing zeros. I am working in a machine shop
example : tolerance for
12. 0 = +/- .030
12.00 = +/- .015
12.000 = +/- .005
The current one was doing great until I introduced a trailing zero 12.01 was 2 places and 12.10 was 1 place need it to count 12.10 as 2. I am setting up nested if statements based on the number of places . I understand normal if statements, I'm just not getting the jest of your formula yet.
If you can show me a way to do it for now I would appreciate it . In the mean time Im going to look up len and mid in the help menu.
Thanks Bob

• Re: Count number of decimal places

Welcome to Ozgrid, Bob.