将前导零/0 添加到特定长度的现有 Excel 值 [英] Add leading zeroes/0's to existing Excel values to certain length

问题描述

关于如何防止在从 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.

有一个字段应该是四个字符，前导零将字符串填充为四个字符.然而:

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"

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

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.

推荐答案

=TEXT(A1,"0000")

但是TEXT 功能是可以的还可以做其他花哨的事情，例如日期格式.

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

这篇关于将前导零/0 添加到特定长度的现有 Excel 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！