如何在格式化十进制数字时保持尾随零? [英] How to keep trailing zeroes in formatting decimal numbers?

问题描述

假设我在记事本中有以下数字:

Suppose that I have the following 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 and paste them in Excel, the numbers 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 want to copy-paste the numbers without changing their original format, for example:

• 0.040，而不是0.04
• 4.0，而不是4
• 456798.7500，而不是456798.75

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

I'm aware that I can use conditional formatting like

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

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

...

Else
   result_else

End If

或使用 Select ... Case Statement ，但是这些方法存在的问题是:

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

1. 代码可能很长，看起来很凌乱.
2. 如果数字发生变化，那么条件条件也必须发生变化.

所以我的问题是:

如何使Excel不截断十进制数字末尾的零?

How to make Excel not truncate the zeroes at the end of decimal numbers?

我想将值保留为数字，而不是文本.

I want to keep the values as numbers, not text.

这种表示形式在财务数据中确实很重要，例如:外汇或货币汇率.

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.

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

这篇关于如何在格式化十进制数字时保持尾随零?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！