Add leading zeroes/0's to existing Excel values to certain length

Mark A picture Mark A · Oct 22, 2010 · Viewed 579.8k times · Source

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.

Answer

GSerg picture GSerg · Oct 22, 2010
=TEXT(A1,"0000")

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