Whole numbers:formatting numbers

I have a list of numbers. Some of them can be very long like 0.156489789 and some can be simply 2, 3, 4, 5 etc...

I want excel to show them to three decimals only when it needs to.

eg: if i have a 1 i want it to read 1
if I have a 3.2 i want it to read as 3.2
if I have a 5.55 i want it to read 5.55
if I have a 1.01515151515 i want it to read formatted like 1.015

I am currently using the number format #,##0.###. This works fine except when I have a whole number like '1' it reads out as '1.' with a decimal after it.

How can I achieve this to read whole numbers without the decimal and still be able to read out the other 1-3 digits if it needs to?


Thanks in advance

In short you can't - at least not with a single Custom Number Format as would appear to be the requirement.

You could use VBA Change Event to apply an appropriate Format based on significance of entry.

In XL2007 and above you can use a Conditional Format to alter Number Format - ie have a rule based on MOD of value with different number formats for whole values and those with decimal remainder.

For sake of ex. ... assume A1:A4 contain the values detailed in original post - with range selected apply rules of:

=INT(A1)=A1
set custom number format to General or # etc...

=MOD(A1,1)
set custom number format to that described in your original post

to reiterate the point though - this is not backwards compatible with versions prior to XL2007