如何在Excel中的数字前输入零

When entering numbers into Excel, zeros at the beginning of the number are removed. This can be an issue when entering phone numbers and IDs. In this article, we will cover ways to solve this problem and keep the leading zeros.

在Excel中输入数字时，数字开头的零将被删除。 输入电话号码和ID时可能会出现问题。 在本文中，我们将介绍解决此问题并保持前导零的方法。

键入时保持前导零 (Keep the Leading Zero as you Type)

If you wanted to ensure that the leading zero is kept when typing, enter a single quote before you type the number.

如果要确保在键入时保留前导零，请在键入数字之前输入单引号。

This instructs Excel to store the value as text and not as a number.

这指示Excel将值存储为文本而不是数字。

When you press “Enter” to confirm, a green triangle is shown in the top left corner of the cell. Excel is checking that you intended to do that or if you want to convert it to a number.

当您按“ Enter”确认时，单元格的左上角会显示一个绿色三角形。 Excel正在检查您是否打算执行此操作，或者是否要将其转换为数字。

Click the diamond icon to display a list of actions. Select “Ignore Error’ to proceed and store the number as text.

单击菱形图标以显示操作列表。 选择“忽略错误”以继续并将号码存储为文本。

The green triangle should then disappear.

然后，绿色三角形将消失。

应用格式以保持零 (Apply Formatting to Keep the Zero)

It is quick and simple to apply text formatting as you type, but it is even more efficient to do it in advance.

在键入时应用文本格式是快速而简单的，但是预先进行格式化会更加有效。

Select the range of cells you want to format as text. Next, click the “Home” tab, select the list arrow in the Number group, and choose “Text.”

选择要设置为文本格式的单元格范围。 接下来，单击“主页”选项卡，在“数字”组中选择列表箭头，然后选择“文本”。

The values you enter into this formatted range will now automatically be stored as text, and leading zeros preserved.

您在此格式化范围内输入的值现在将自动存储为文本，并保留前导零。

使用自定义格式将其保留为数字 (Using Custom Formatting to Keep it Numeric)

The previous two options are great and sufficient for most needs. But what if you needed it as a number because you are to perform some calculations on it?

前两个选项非常适合大多数需求。 但是，如果由于要对其进行一些计算而需要将其作为数字怎么办？

For example, maybe you have an ID number for invoices you have in a list. These ID numbers are exactly five characters in length for consistency such as 00055 and 03116.

例如，也许您具有列表中发票的ID号。 为了保持一致性，这些ID号正好是五个字符，例如00055和03116。

To perform basic calculations such as adding or subtracting one to increment the invoice number automatically, you would need it stored as a number to perform such a calculation.

要执行基本计算，例如加一或减一以自动增加发票编号，您需要将其存储为编号以执行此计算。

Select the range of cells you want to format. Right-click the selected range and click “Format Cells.”

选择要格式化的单元格范围。 右键单击所选范围，然后单击“设置单元格格式”。

From the “Number” tab, select “Custom” in the Category list and enter 00000 into the Type field.

在“数字”选项卡上的“类别”列表中选择“自定义”，然后在“类型”字段中输入00000。

Entering the five zeros forces a fixed-length number format. If just three numbers are entered into the cell, two extra zeros are automatically added to the beginning of the number.

输入五个零将强制采用固定长度的数字格式。 如果仅在单元格中输入了三个数字，则会在数字的开头自动添加两个额外的零。

You can play around with custom number formatting to get the exact format you require.

您可以使用自定义数字格式来获得所需的确切格式。

---

---

Excel’s ability to format cells is an excellent tool to provide consistent formatting of phone numbers, credit card numbers, and IDs—especially when the data is entered by multiple people.

Excel格式化单元格的能力是一种出色的工具，可提供一致的电话号码，信用卡号和ID格式，尤其是当多个人输入数据时。

翻译自: https://www.howtogeek.com/434261/how-to-enter-zero-before-a-number-in-excel/