The Zip Code Conundrum
In Excel, when you have a 0-first digit zip code, it usually shows up as only the last four digits. So, take a Massachusetts zip code like 02201. If you just type that into an Excel cell it comes up 2201. You could type an apostrophe (‘) in front of it, but then it would appear left justified like all other text making your list look disjointed. Depending upon what you want to ultimately do with this data, there are several alternatives to living with the above two situations.
I just want to use the data in Excel.
If you’re just going to use it in Excel for sorting, filtering and Pivot Tables, the right answer is probably the special format for Zip Codes. In Home>Number, click on the dialog box launcher. Or, you can use Ctrl+1 or right-click>Format Cells. This brings up the Format Cells dialog box. Be sure you are on the Number tab in the dialog box and select the Special category. You will see a selection for Zip Code. In reality, this correction just makes the leading zero appear. It is not actually in the cell, it just looks like it. However, this is enough of a fix for just about anything you want to do in Excel, including Pivot Tables.
I want to use my data in a Mail Merge
The above solution does not take care of the problem for Mail Merge. When you attempt to use an Excel list containing four digit/leading zero zip codes, it will just show the four numbers in your mail merge results. To fix this, it will require a bit of fancy footwork with formulas. In a blank column, use the TEXT function to change the contents of your Zip Code column to numbers stored as text. The TEXT function requires a format_text argument which, in this case, would be “00000.” The difference between this solution and the special Zip Code format solution above is that it actually adds the zero and then converts it to a text format, so that it holds on to it, similar to typing an apostrophe.
I want to export my data to Access
Use the Mail Merge solution above. After importing your records to Access, you may change the Zip Code column format to the Access Zip Code format.
