Tweak a custom number format (hiding decimal point)

[Harribone]

Hi,

Hopefully this is an easy one. I have a cell with a custom number format 0.##.

So if the result in this cell has one or two decimal places it will show those decimal places (e.g. 4.5, 4.55).
However when the value is a whole number the decimal point is shown (e.g. '4.' instead of '4').

This is the first time I've had the need to use a custom number format so not sure if it is something silly I've missed or not. Also this has only been checked in 2013 Excel so far and would need to be backward compatible to 2007.

Any other workarounds would be greatly appreciated too.

Thanks
Harribone

[pareshj]

Hi,

Here is one closest custom format : [=4]4;.##

But the problem with above is when you enter 4 it will show as 4 only but if you enter any other number it will show decimal point.

Also, Checked the below formula and if you can use formula in place, you can use the below formula:

=IF(ROUND(A1,2)=INT(A1),TEXT(A1,"0"),TEXT(A1,"0.0#"))



Regards,
Paresh J

[Harribone]

Thanks pareshj!

[pareshj]

Please mark this thread as SOLVED and click * to say thanks.


Regards,
Paresh J