添加前导零/0到现有的Excel值到一定长度。

There are many, many questions and quality answers on SO regarding how to prevent leading zeroes from getting stripped when importing to or exporting from Excel. However, I already have a spreadsheet that has values in it that were truncated as numbers when, in fact, they should have been handled as strings. I need to clean up the data and add the leading zeros back in.

关于如何防止在导入或导出Excel时取消引导零，有许多问题和质量问题的答案。但是，我已经有一个电子表格，它的值被截断为数字，实际上，它们应该被作为字符串处理。我需要清理数据，并添加前导零。

There is a field that should be four characters with lead zeros padding out the string to four characters. However:

有一个字段，应该是四个字符，其中的lead zeros将字符串填充为四个字符。然而:

"23" should be "0023", 
"245" should be "0245", and
"3829" should remain "3829"

Question: Is there an Excel formula to pad these 0's back onto these values so that they are all four characters?

问:是否有一个Excel公式可以让这些0回到这些值上，使它们都是四个字符?

Note: this is similar to the age old Zip Code problem where New England-area zip codes get their leading zero dropped and you have to add them back in.

注意:这类似于新英格兰地区邮政编码的旧邮政编码问题，而新英格兰地区的邮政编码将会被删除，你必须把它们重新添加进去。

6 个解决方案

#1

=TEXT(A1,"0000")

However the TEXT function is able to do other fancy stuff like date formating, aswell.

然而，文本功能还可以做其他一些奇特的事情，比如日期格式。

#2

The more efficient (less obtrusive) way of doing this is through custom formatting.

实现这一点的更有效(不那么引人注目)的方法是通过自定义格式。

1. Highlight the column/array you want to style.
2. 突出显示您想要样式的列/数组。
3. Click ctrl + 1 or Format -> Format Cells.
4. 单击ctrl + 1或格式->格式单元格。
5. In the Number tab, choose Custom.
6. 在Number选项卡中，选择Custom。
7. Set the Custom formatting to 000#. (zero zero zero #)
8. 将自定义格式设置为000#。(零#)

Note that this does not actually change the value of the cell. It only displays the leading zeroes in the worksheet.

注意，这实际上并没有改变单元格的值。它只显示工作表中的前导零。

#3

I hit this page trying to pad hexadecimal values when I realized that DEC2HEX() provides that very feature for free.

You just need to add a second parameter. For example, tying to turn 12 into 0C
DEC2HEX(12,2) => 0C
DEC2HEX(12,4) => 000C
... and so on

当我意识到DEC2HEX()提供了一个免费的特性时，我就尝试用它来填充十六进制值。您只需要添加第二个参数。例如，将12变为0C DEC2HEX(12,2) => 0C DEC2HEX(12,4) => 000C…等等

#4

I am not sure if this is new in Excel 2013, but if you right-click on the column and say "Special" there is actually a pre-defined option for ZIP Code and ZIP Code + 4. Magic.

我不确定这是否在Excel 2013中是新的，但是如果你右键点击这个列并说“Special”，实际上有一个预先定义的ZIP Code和ZIP Code + 4的选项。魔法。

#5

I know this was answered a while ago but just chiming with a simple solution here that I am surprised wasn't mentioned.

我知道这是刚才的回答，但只是用一个简单的解决方案，我很惊讶没有提到。

=RIGHT("0000" & A1, 4)

Whenever I need to pad I use something like the above. Personally I find it the simplest solution and easier to read.

当我需要垫的时候，我就用上面的东西。我个人认为它是最简单的解决方案，更容易阅读。

#6

If you use custom formatting and need to concatenate those values elsewhere, you can copy them and Paste Special --> Values elsewhere in the sheet (or on a different sheet), then concatenate those values.

如果您使用自定义格式，并且需要将这些值连接到其他地方，您可以复制它们并粘贴特殊的——在表中其他地方的>值(或在不同的表上)，然后将这些值连接起来。