Missing leading zeroes in XLS

If you are not careful when importing data into Excel (XLS), you often see leading zeroes be removed as XLS tries to be smart about the data it imports. However, this causes issues in Zip codes, SSNs, account numbers, etc. E.g. east coast or Puerto Rico Zip codes start with one or two leading 0s, and suddenly ZIP code 00555 turns into 555. This of course creates all sorts of fun issues in comparison, or if data is used as input into other processes, etc.

To append leading zeroes to a field, simply add a new column with the following formula, and then copy/paste the new column as values to save it. The formula works on any number of leading zeroes missing. Note that “5” in the formula below is the number of digits you expect in the correct length of the field (e.g. 5 for ZIP)

 A       B
ZIP   Fixed_ZIP
555   =RIGHT(“00000” & A2,5)

To add extra protection in case there are blank ZIP codes, simply change the formula to:
=IF(TRIM(A2)="","",RIGHT("00000"&A2,5))

FYI, the simplest way to avoid the leading zeroes being removed is to import the field as text. This is done in the "Text Import Wizard" in XLS that launches automatically when you open a text file. In step 3, simply select set the ZIP fields as text.

Nifty,
Thomas