如何使Excel在格式化十进制数时不截断0？ [英] How to make Excel doesn't truncate 0's in formatting decimal numbers?

问题描述

假设我在记事本中有以下随机数：

Suppose that I have the following random numbers in Notepad:

1.19
0.040
10.1123
23.110
21.223
2.35456
4.0
10234.0009
456798.7500
123
34.560
40060
33.7876

如果我复制上面的数字并将其粘贴到Excel中，数字格式将更改为此

If I copy the numbers above and paste them in Excel, the number formats will change to this

1.19
0.04
10.1123
23.11
21.223
2.35456
4
10234.0009
456798.75
123
34.56
40060
33.7876

我打算复制粘贴数字而不更改其原始格式，例如：

I intend to copy-paste the numbers without changing their original formats, for examples:

• 0.040保持0.040，而不是0.04;

• 0.040 keeps 0.040, not 0.04;

4.0保持4.0，而不是4;

4.0 keeps 4.0, not 4;

我知道我可以使用条件格式，如

I'm aware that I can use conditional formattings like

If condition_1 Then
   Cells(...).NumberFormat = "0.00"

ElseIf condition_2 Then
   Cells(...).NumberFormat = "0.000"

...

Else
   result_else

End If

或使用选择...案例陈述，但这些方法的问题是：

or use Select ... Case Statement, but the problems with these methods are:

1. 代码可能很冗长，看起来很乱。


2. 如果数字发生变化，则条件也必须更改

所以我的问题是：

如何使Excel不会截断十进制数结尾的0？

How to make Excel doesn't truncate the 0's in the end of decimal numbers?

PS 我想保留值作为数字，不文本。

P.S. I want to keep the values as numbers, not texts.

附录：我不明白为什么一个downvote这个OP，因为这种财务数据的重要性如：外汇或汇率。

Addendum: I don't understand why does one downvote this OP since this kind of representation does matter in financial data for example: foreign exchange or currency rate.

推荐答案

如果我将原始数据放在列 A （使用您的发布格式）并运行：

If I place your original data in column A (with your posted formats) and run this:

Sub CopyFull()
    Dim A As Range, B As Range

    Set A = Range("A1:A13")
    Set B = Range("B1:B13")
    A.Copy B
End Sub

复制的数据项将具有相同的格式原件：

The copied data items will have the same formats as the originals:

所以如果 A9 有一个 NumberFormat 0.0000 ，那么 B9 。

So if A9 has a NumberFormat of 0.0000, then so will B9.

编辑＃1：

如果数据在一个打开的NotePad过程中开始，我会：

If the data started out in an open NotePad process, I would:

1. 手动（或以编程方式）将数据存储为文本文件（ .txt ）


2. 将文本文件导入列，将文本


3. 将列中的每个项目转换为号码与 NumberFormat 符合文本格式

1. manually (or programmatically) store the data as a text file (.txt)
2. import the text file into a column as Text
3. convert each item in the column into a Number with a NumberFormat consistent with the text format

这篇关于如何使Excel在格式化十进制数时不截断0？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！