Remove insignificant zeros after the decimal point

Question

I have a table of values with decimals and whole numbers. Some of the decimals have zeros two places after the decimal point (e.g. 0.60) or the two places after the decimal point are both zeros (e.g. 4.00).

How do I make sure any zeros are removed from after the decimal point? (So the aforementioned numbers would be 0.6 and 4.)

Answer 1

You need to define your own "number format". The format code you want to use is:

0.####

EDIT

---

The downside is you are always left with the trailing period, which cannot be handled by number formats alone. To remedy that, you can use the following function.

=SUBSTITUTE(TRIM(SUBSTITUTE(A1,"."," "))," ",".")

Results

---

Original  Formatted:  Trimmed:
Numbers:
===============================
17.000    17.         17
17.100    17.1        17.1
17.100    17.1        17.1
0.100     0.1         0.1

References

---

1. Create a custom number format, Accessed 2014-06-25, <http://office.microsoft.com/en-ca/excel-help/create-a-custom-number-format-HP010342372.aspx>

Answer 2

The General format will not show any trailing decimal zeros. Regardless of whether the number is entered manually or calculated, if the cell format is General, Excel will only show the decimals required to represent the number.