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

问题描述

关于如何在导入到Excel或从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功能能够做其他奇特的事情，如日期格式，aswell。

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

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