Number Format With Fixed Number Of Digits

[navajo_23]

Hi!

How shall I determine the number format to have it fixed independently of where the comma is?
I would do like to have all the cells with 6 digits as follows:
123456
12345.6
1234.56
123.456
12.3456
1.23456
0.12345
0.01234
0.00123
0.00012
0.00001

Many thanks,

[Jacc]

-----------

[teylyn]

Hello,

all the numbers of your sample will behave like that with the General format.

Is this really your question? Or do you need to shorten numbers with more than 6 digits to only 6 digits?

cheers, teylyn

[navajo_23]

Hi Teylyn,

Thank you for your reply and sorry for not being precise enough.

My numbers can have more than six digits (however never before the comma).
for example:
123.521456874 should show: 123.521
1.25879632124 should show: 1.25880
58743.2158795 should show: 58743.2
0.02598763527 should show: 0.02599
65.5875135843 should show: 65.5875
432879.012587 should show: 432879
...

[teylyn]

Hi again,

you could use a formula like

=TRUNC(A1,6-LEN(INT(A1)))

This will truncate the number.

Or you could use six conditional formats that test how many digits are in front of the decimal point. The conditions and custom formats are listed below. Note that these will round the number, not just cut off the remaining decimals.

Please Login or Register  to view this content.

[navajo_23]

Hi Teylyn,
I worked perfectly with the conditional formatting, the trunc function made the formulas unclear.
Thank you very much!