logo标签列表

将现有的Excel数值前面添加0，使其达到特定长度

excelpadding
326

在SO上有很多关于如何防止Excel导入或导出时去掉前导零的问题以及如何解决这个问题的高质量回答。然而，我已经有一个电子表格了，其中的值在处理为数字时被截断，而实际上它们应该被视为字符串处理。我需要清理数据并将前导零添加回去。

有一个字段应该是四个字符，并且由前导零填充以使字符串达到四个字符。然而：

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

问题：有没有一种Excel公式可以将这些0添加回这些值，以使它们都是四个字符？

注意：这类似于古老的邮政编码问题，其中新英格兰地区的邮政编码会将前导零删除，您需要将它们添加回去。

在“3829.”中应该有句点吗？ - Josh Lee
1
不，但是根据我可靠的《斯特朗克和怀特》副本，在句子中放置“.”字符的位置是正确的。为了清晰起见，已将其删除。 - Mark A
8
我可以承认这个观点，但因为它在代码块中，我倾向于更加字面理解。另外，Excel将每个数字都存储为浮点数，所以可能会有小数位。 - Josh Lee
6
感谢 Josh 正确拼写了“accept that except that”，给他点个赞。 - Michiel van der Blonk
8个回答
549
=TEXT(A1,"0000")

然而，TEXT 函数 可以执行其他高级操作，例如日期格式化。

4
哇！太完美了！每天都能学到新东西。我甚至有多本用旧的 Excel 书籍，但从未遇到过这个问题——以后只要出现 Excel 基础的邮政编码数据混乱，我就会使用这种方法。 - Mark A
1
这对于十进制数很有效...但是十六进制呢？我找不到让前导0生效的方法... - Shadow
11
在Excel中，十六进制数字被视为字符串而非数字。因此，请将其视为字符串处理：=RIGHT("0000" + A1, 4)。 - GSerg
8
@shadow，DEC2HEX 函数需要一个 places 参数。因此，=DEC2HEX(HEX2DEC(A1),4) 可以很好地填充十六进制数。 - Raz Wilson
9
他们没有改变语法。你的版本的Excel，恰好使用了分号(;)作为参数分隔符，这是根据你的Windows语言设置来确定的。 - GSerg
显示剩余5条评论
82

更加高效（不那么显眼）的方法是通过自定义格式。

  1. 高亮你想要设置样式的列或数组。
  2. 按下 ctrl + 1 或选择 格式 -> 单元格格式。
  3. 在数字选项卡中，选择自定义。
  4. 将自定义格式设置为000#（零零零 #）。

请注意，这并不实际改变单元格的值。它仅仅在工作表中显示前导零。

10
如果不需要保留原单元格中前导零的话，这种方法很有效，但如果需要在其他地方使用这些字符串，则不能使用该方法。这将使单元格显示为0004、01032、0284，但是如果您尝试在另一个单元格中组合这些字符串，它将显示为4-1032-284，而不是所需的0004-01032-0284。 - Ashton Sheets
@AshtonSheets 是的，正如我在答案中所指出的那样，它只显示前导零，对实际值没有任何影响。然而，有些情况下我使用它代替=text()，因为它更加方便且不涉及公式。 - Moses
8
参考资料，实际上应该是“0000”，而不是“000#”。否则对于数字0无法正常工作。 - PearsonArtPhoto
我使用这个解决方案的变体来将金额舍入到百万美元：$#,,"M"。但是，当我想要的是$0.2M时，它会将$200,000转换为$.2M。然后我使用$0#,,"M"，它给我提供了$0.2M格式，但也将大于1,000,000的数字更改为$01.0M格式！我似乎在打转 - 有没有办法在小数点前面加上0？ - Andrew
2
对于那些遇到我上述问题的人，答案是将#（井号）替换为0（零）。当没有值时，#不会给你任何字符，但是0会给你一个零！这很合理，也非常有效：$0.0,,"M" - Andrew
1
对我来说，使用000#也没有起作用，但当我只指定自定义格式为0000（四位数）、00000（五位数）、000000（六位数）等时，它就起作用了。显然，这仅适用于较短的格式数字。 - Hilary
16

我在尝试填充十六进制值时遇到了这个页面，后来我意识到，DEC2HEX()本身就提供了这个功能。

你只需要添加第二个参数。例如，将12转换为0C
DEC2HEX(12,2) => 0C
DEC2HEX(12,4) => 000C
...等等

14

我知道这个问题有一段时间没有得到回答了，但是我想提供一个简单的解决方案，令人惊讶的是之前没有被提到过。

=RIGHT("0000" & A1, 4)
无论何时我需要填充时，我都会使用类似上面的东西。就我个人而言，我发现这是最简单的解决方案，并且更易于阅读。

每当我需要填充时，我都会使用类似以上的方法。就我个人而言，我发现这是最简单的解决方案，也更易于阅读。

这对于填充原始文本非常有效，而在我的情况下，该文本是十六进制数字。 - Derek
难道不应该是=RIGHT("0000" & A1, 4)吗？ - Tms91
9
我不确定这在Excel 2013中是否是新功能，但是如果您右键单击该列并选择“特殊”，实际上有预定义的选项可供选择，包括邮政编码和邮政编码+4。非常神奇。 enter image description here
6
好的建议和更新。微软鼓励任何人将社会安全号码存储在电子表格中，这一点需要给予微软负面评价。 - Mark A
1
即使这样也能很好地工作。
REPT(0,2-LEN(F2)&F2

其中2是数字的总位数，对于0到9的数字，它将显示00到09，其他数字不会添加任何内容。

1
如果您使用自定义格式并需要在其他地方连接这些值，则可以将它们复制并“粘贴特殊”——>值到工作表的其他位置（或不同的工作表），然后连接这些值。
0
假设您想要填充的数字在单元格A1中，"填充的零的数目"为4 ，
例如：
- "23" 应该变成 "0023" - "245" 应该变成 "0245"，而 - "3829" 仍然是 "3829"
那么，
=TEXT(A1,REPT("0",4))
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接