CODEWITHSUNDEEP
excel
user112947
user112947

Reputation: 473

Remove insignificant zeros after the decimal point

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.)

Upvotes: 5

Views: 36858

Answers (4)

Fernando
Fernando

Reputation: 111

If you need the results in a simple way use this function with "General" formatting:

=ROUND(A1,2)

This will create the proper result

Note: In Portuguese version the name of the function is ARRED and you need to use semicolon to separate values as follows:

=ARRED(A1;2)

The values will be converted as follows:

Original            ROUND
--------------------------------
0.60                0.6
4.00                4
0                   0
7.75                7.75
0.016666667         0.02
5.833333333         5.83
2.673333333         2.67
699                 699

Upvotes: 1

pnuts
pnuts

Reputation: 59485

Please try: 

=INT(A1*100)/100

and copy down to suit, assuming your data is in ColumnA and that formatting is 'General'.

A comparison of various possibilities:

SO24417712 example

[It seems that for the accepted A to be correct the Q may be off topic (since @teylyn's solution would work also, is much simpler and requires no programming) - unless the objective is to convert to strings, which is not mentioned as a requirement.]

Upvotes: 1

teylyn
teylyn

Reputation: 35970

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.

Upvotes: 4

Cloud
Cloud

Reputation: 19323

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>

Upvotes: 8

Related Questions