将前导零/0 添加到现有 Excel 值至一定长度
关于如何防止导入或导出 Excel 时前导零被删除,SO 上有很多很多问题和高质量的答案。但是,我已经有一个电子表格,其中的值被截断为数字,而实际上,它们应该作为字符串处理。我需要清理数据并将前导零添加回来。
有一个字段应该是四个字符,前导零将字符串填充为四个字符。但是:
"23" should be "0023",
"245" should be "0245", and
"3829" should remain "3829"
问题:是否有 Excel 公式可以将这些 0 填充回这些值,以便它们都是四个字符?
注意:这类似于古老的邮政编码问题,其中新英格兰地区的邮政编码的前导零被删除,您必须将它们重新添加。
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.
There is a field that should be four characters with lead zeros padding out the string to four characters. However:
"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?
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(8)
但是,TEXT 函数还可以执行其他操作还有像日期格式这样的奇特东西。
However the TEXT function is able to do other fancy stuff like date formating, aswell.
更有效(不那么唐突)的方法是通过自定义格式。
请注意,这实际上并没有改变单元格的值。它仅显示工作表中的前导零。
The more efficient (less obtrusive) way of doing this is through custom formatting.
Note that this does not actually change the value of the cell. It only displays the leading zeroes in the worksheet.
我知道这个问题不久前就得到了回答,但只是在这里提出了一个简单的解决方案,令我惊讶的是没有提到。
每当我需要填充时,我都会使用类似上面的东西。我个人认为这是最简单的解决方案并且更易于阅读。
I know this was answered a while ago but just chiming with a simple solution here that I am surprised wasn't mentioned.
Whenever I need to pad I use something like the above. Personally I find it the simplest solution and easier to read.
当我意识到
DEC2HEX()
提供了这个功能时,我尝试填充十六进制值,免费。您只需要添加第二个参数。例如,绑定将
12
转换为0C
DEC2HEX(12,2)
=>0C
DEC2HEX(12,4)
=>000C
... 等等
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
into0C
DEC2HEX(12,2)
=>0C
DEC2HEX(12,4)
=>000C
... and so on
我不确定这是否是 Excel 2013 中的新功能,但如果您右键单击该列并说“特殊”,实际上有一个邮政编码和邮政编码 + 4 的预定义选项。Magic。
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.
假设您要填充的数字位于单元格 A1 中,并且“填充零的数量”为 4 ,
例如
那么
Assuming that the number you want to pad is in cell A1, and the "padding number of zeros" is 4 ,
e.g.
then
如果您使用自定义格式并需要在其他地方连接这些值,您可以复制它们并选择性粘贴 -->工作表中其他位置(或不同工作表上)的值,然后连接这些值。
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.
即使这样也能很好地工作
,其中 2 是总位数,对于 0 ~ 9 -> 0它将显示 00 到 09 其余的不会添加任何内容。
Even this will work nicely
where 2 is total number of digits, for 0 ~ 9 -> it will display 00 to 09 rest nothing will be added.