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

Question

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 1

=TEXT(A1,"0000") 

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

Answer 2

The more efficient (less obtrusive) way of doing this is through custom formatting.

1. Highlight the column/array you want to style.
2. Click ctrl + 1 or Format -> Format Cells.
3. In the Number tab, choose Custom.
4. Set the Custom formatting to 000#. (zero zero zero #)

Note that this does not actually change the value of the cell. It only displays the leading zeroes in the worksheet.