在小数点后删除无效的零

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

我有一个带小数和整数的值表。一些小数点在小数点后两位有零（例如0.60）或小数点后两位都是零（例如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.)

如何确保在小数点后删除任何零？（所以前面提到的数字是0.6和4）

4 个解决方案

#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

参考

Create a custom number format, Accessed 2014-06-25, <http://office.microsoft.com/en-ca/excel-help/create-a-custom-number-format-HP010342372.aspx>
创建自定义数字格式，访问2014-06-25，

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

常规格式不会显示任何尾随的十进制零。无论是手动输入还是计算数字，如果单元格格式为常规，Excel将仅显示表示数字所需的小数。

#3

Please try:

请尝试：

=INT(A1*100)/100

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

假设您的数据在ColumnA中并且格式为“常规”，则向下复制以适应。

A comparison of various possibilities:

各种可能性的比较：

[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.]

[似乎对于被接受的A是正确的，Q可能不在主题上（因为@ teylyn的解决方案也可以工作，更简单并且不需要编程） - 除非目标是转换为字符串，这没有提到需求。]

#4

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，您需要使用分号分隔值，如下所示：

=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

#1