如何防止Excel计算单元中前导零的自动截断

If I paste 04562, it automatically truncates the leading zero and stores it as 4562.

如果我粘贴04562，它会自动截断前导0并将其存储为4562。

I want it to to store it as 04562.

我想把它存储为04562。

13 个解决方案

#1

If you are using MS Excel to edit the cells where you need to store the numbers with leading zero, just format and define the cell content as TEXT. If you are storing it programmatically, you can put the value in a quote '' for the same.

如果您正在使用MS Excel编辑需要存储前导为零的数字的单元格，只需将单元格内容格式化并将其定义为文本。如果您正在以编程方式存储它，您可以将值放在引号中“for the same”。

Update

更新

I just noticed that if I go to "Format Cells" and select custom format and put, say 0000 to the format, any number that I put in the cell, it is is less than 4 digits, it still maintain the 4 digit length with the leading '0', like 0001, 0099, 0120, 9999

我只是注意到,如果我去“格式细胞”并选择自定义格式,把,说0000格式,任何数量,我把在细胞中,它是小于4位数,它仍然保持4位数的长度与领先的“0”,就像0001年,0099,0120,9999

#2

While all of this is accurate workarounds for converting a number to text so that the leading zeros will be preserved, it is not an answer to the question.

虽然所有这些都是将数字转换为文本的精确变通方法，以便保留前导0，但这并不是问题的答案。

The answer to your question is that if you want a number with leading zeros in Excel, you cannot have it - at least during the import.

您的问题的答案是，如果您希望Excel中有一个带前导0的数字，那么您就不能使用它——至少在导入期间是这样。

The Microsoft developers, for whatever reason built Excel to read in 03 as a number and truncate the leading zero, resulting in 3. There is no way to prevent this.

不管出于什么原因，微软的开发人员构建了Excel，以03作为数字读取，并截断了0的前导，导致3。这是无法避免的。

It is unfortunate because all workarounds assume you know what columns have values that might have leading zeros so that you can take steps * after the fact * to try and fix your data.

不幸的是，所有的工作区都假定您知道哪些列的值可能有前导0，以便您可以在事件发生后执行步骤*以尝试修复数据。

By the way, Google Sheets does the same thing.

顺便说一下，谷歌表也做同样的事情。

So the short answer is that there is no spreadsheet application (at least that I am aware of) where you can read in a csv file that might have oh, say 00453 as a number and have it imported as 00453 and not 453.

简短的回答是，没有电子表格应用程序(至少我知道)可以在csv文件中读取，比如00453，然后导入为00453，而不是453。

#3

Click the cell and On the Home tab, in the Number group, click the dialog box launcher Button image next to Number.

单击单元格，在数字组的Home选项卡上，单击数字旁边的对话框启动按钮图像。

In the Category list, click Custom and then, in the Type box, type the number format, such as 000-00-0000 for a social security number code, or 00000 for a five-digit postal code.

在类别列表中，单击Custom，然后在类型框中键入数字格式，比如社会保险号代码为00-0000，或者五位数邮政代码为00000。

https://support.office.com/en-us/article/Keep-leading-zeros-in-number-codes-1bf7b935-36e1-4985-842f-5dfa51f85fe7

https://support.office.com/en - us/article/keep领先- 0 -数量-编码- 1 - bf7b935 - 36 - e1 - 4985 - 842 - f - 5 - dfa51f85fe7

#4

You can save it using the following format.

您可以使用以下格式保存它。

In excel, you can

1) set the format to "Text" by selecting the proper cell, right clicking, selecting "Format Cell". Then under the "Number" tab, select "text". Anytime you enter a number, it will be read as text

1)选择合适的单元格，右键点击，选择“format单元格”，将格式设置为“Text”。然后在“数字”选项卡下，选择“文本”。任何时候你输入一个数字，它都会被当作文本来读

OR

或

2) precede any number with an apostrophe (') and Excel will accept it "as is"

2)在任何带撇号(')的数字之前，Excel将接受“原样”

If you are using VBA, you can do the following

1) Select the cell you need to format and change the number format option

1)选择需要格式化的单元格并更改number format选项

Range("A1").NumberFormat = "@"

范围(" A1 ")。NumberFormat = " @ "

When you read the data (from a cell in Excel or from VBA), it will contain any leading zeros that were entered.

当您读取数据(从Excel或VBA中的单元格读取)时，它将包含所有输入的前导0。

#5

I thought it might be helpful to do a step by step for some people that might need a little more help finding where to select these things.

我想，对于那些需要更多帮助才能找到这些东西的人来说，一步一步地去做可能会有帮助。

1. Select the square or squares you need by clicking on them/highlighting them.

   通过单击它们/突出显示它们，选择需要的正方形或正方形。

2. Stay on the Home tab up at the top. All the way on the right side, on the second box to the right, there is an option that says Format. Click on that.

   呆在顶部的Home选项卡上。在右边，在右边的第二个方框中，有一个选项是Format。点击。

3. Click on Format Cells... all the way at the bottom. The first tab should say Number. On that Number tab, there's an option that says Text. Click on it. Now, press OK at the bottom, and done.

   点击格式细胞……一直到底部。第一个选项卡应该是Number。在这个数字标签上，有一个选项是“文本”。点击它。现在，在底部按OK，完成。

#6

If you just double click on a csv text file to open it, Office 2010 will open it and truncate any leading zeroes because it is general formatting every column by default. Instead, you can IMPORT the file: Click on the "Data" tab - the "Get External Data" options include "From Text" - that opens a wizard where you can tag the file to import, preview it, and define the format for any column containing lead zeroes as "text." When you import this way, the leading zeroes are preserved.

如果您只是双击一个csv文本文件来打开它，Office 2010将打开它并截断任何前导0，因为它是默认的所有列的通用格式。相反，您可以导入文件:单击“Data”选项卡——“获取外部数据”选项卡包括“From Text”——打开一个向导，在那里您可以标记文件以导入、预览它，并将包含前导零的任何列的格式定义为“Text”。当您以这种方式导入时，前导0将被保留。

#7

You can also just prefix your value with a ', though this flags the cell until you make it a text field.

您还可以用'作为值的前缀，尽管这将标记单元格，直到您将其设置为一个文本字段。

#8

Here was my best and easiest solution. Because Excel will change the numbers if you already have the data typed in. Only works if you type in after changing the format.

这是我最好、最简单的解决方案。因为如果你已经输入了数据，Excel将会改变数字。只有在更改格式后输入时才有效。

1. Add a new column
2. 添加一个新的列
3. Highlight the column and change format to TEXT via right clicking and format cells
4. 通过右键单击和格式化单元格突出显示列和更改格式。
5. Take your zip codes or numbers clumn and copy it
6. 取你的邮政编码或号码块并复制
7. Paste into Notepad (to get rid of formatting)
8. 粘贴到记事本(以避免格式化)
9. Copy the notepad data and paste into your new Excel column
10. 复制记事本数据并粘贴到新的Excel列中

#9

I don't know if this is perfect work around but, I tried it the first time and it worked.

我不知道这是不是一个完美的工作，但是，我第一次尝试，它成功了。

On the left cell you don't see leading 0 and I wanted to add leading 0. The fix is to use the formula ="0"&A3(Cell number)

在左边的单元格中你看不到前导0我想要添加前导0。修正方法是使用公式="0"和a3(单元号)

(remember to set your and it should work perfectly see example below.

(记住要设置你的，它应该能很好地工作，请看下面的例子。

#10

Adding an apostrophe before the number that starts with a zero will keep the full number but not post the apostrophe. For example: if your number is 08753 excel will truncate the number to 8753 unless you type '08753. Excel will then fill the cell with 08753. Easy!

在以0开头的数字之前添加撇号将保留完整的数字，但不会发布撇号。例如:如果您的数字是08753,excel将把数字截断为8753，除非您输入'08753。然后Excel将在单元格中填充08753。简单!

#11

If the amount you are going to input will have a fixed amount of digits, let's say for example, zip code which is 5 digits long, then you can apply a customized format to this cell with the mask: 00000. With this, no matter what amount, which does not exceed 5 digits long, you input, the leading zeroes will be kept.

如果您要输入的数量是固定的，例如，邮政编码是5位数，那么您可以使用遮罩:00000对该单元格应用定制格式。有了这个，无论什么数量，不超过5位数字，你输入，前导零将被保留。

1. Right-clic on the cell to format.
2. 单元格上的右对齐格式。
3. Clic on Cells Format.
4. 按细胞上的格式。
5. Choose Number tab.
6. 选择选项卡。
7. Clic on Customize option.
8. 按自定义选项。
9. Clic on any the options listed on the listbox on the right.
10. 在右侧列表框中列出的任何选项上的Clic。
11. Clic on Type texbox, and overwrite its content with "00000".
12. Clic类型为texbox，并使用“00000”覆盖其内容。
13. Clic on Ok.
14. 按Ok。

#12

If you are importing / opening a .csv file that has leading zeroes, use Open Office or Libre Office. When you open the file, it will present a dialog to specify things like the column delimiter. You can also select one of the columns and specify the type as "text" which will preserve the leading zeroes.

如果您正在导入/打开一个具有前导0的.csv文件，请使用Open Office或Libre Office。当您打开文件时，它将显示一个对话框来指定诸如列分隔符之类的内容。您还可以选择其中一列，并将类型指定为“text”，它将保留前导0。

If you then save it as a .xlsx file, it can be opened with Excel and will preserve the leading 0.

如果您将它保存为.xlsx文件，可以使用Excel打开它，并保留前导0。

It seems like there should be a way to specify this on import to Excel but I haven't been able to find it.

似乎应该有一种方法在import to Excel中指定这个，但是我还没找到。

#13

The problem is actual stored values vs. displayed values... ALL of these presentation "fixes" do not address the loss of leading zeros in the stored value.

问题是实际存储的值与显示的值……所有这些表示“修复”都不能解决存储值中前导零的损失。

So if you are entering a value into a cell or importing the end result is the same, you cannot preserve the leading zeros in the stored value REGARDLESS of formatting constraints on the cell.

因此，如果您将一个值输入到单元格中或导入最终结果是相同的，那么无论单元格上的格式化约束如何，您都不能在存储值中保留前导0。