My old zip code in Stamford was 06902. My current zip code also has a leading zero. It happens, and it can be frustrating when you’re working with spreadsheets that will eventually be merged to mailing labels.
I’ve helped a number of folks who aren’t Google/Excel pros who struggled with this issue so I thought it might be a good time to blog my simple fix.
When Excel (or Google Docs) sees just numbers in a field, it assumes you want the field to be a number. However, with zip codes you don’t want the field to be formatted as a number because then 06902 is written as 6902. You want those fields to be set as text so the leading zeroes are not dropped. Besides, when was the last time you ever had to add two zip codes together?
In Excel, it’s easy to format a column as zip code and the leading zeroes are respected.
But if you want to bring that spreadsheet to Google Docs, all that goes out the window because there’s no special setting in Google for zip codes. I’m doing this right now to start building a Bat Mitzvah guest list I can work on with with family members that originated as an Excel file I used for mailing labels for the last Bat Mitzvah two years ago. I can convert the column to text before uploading to Google, but that won’t magically add zeroes that weren’t there before.
Sure, you can just wait until you do the final mail merge in Excel to format the zip column again, but here’s an easy way to have the zip codes display correctly in Google Docs and not worry about it later.
1. Import the spreadsheet as-is, knowing the zip codes are going to come in as numbers.
2. Highlight the zip code column and format it as plain text. You’ll know because it will move from right justified to left.
3. Create a column just to the right of the zip code column. Header it “Corrected Zip Code”
4. In the first cell, enter this formula (assuming the zip codes are in column G, change to match for your own spreadsheet): =if(len(G2)=4,”0″&G2,G2)
Simply, if the length of the data in column G is 4 characters, let’s say 1234, then the cell will return 01234, otherwise it will just return whatever is already in the cell.
5. Copy this formula down all the cells in the column. The formula will automatically adjust to copy the correct adjacent cell.
6. Looks great, but you’re not quite done yet. You don’t want the confusion of both zip code columns hanging around. Highlight over the new column you just created, right-click and select “Copy”
7. Go to Google Doc’s Edit menu and select “Paste Values Only”
It won’t look visibly different, but you want to do this to break the link between the old column and the new one you just created.
8. Now you can safely delete the original zip code column and the corrected values remain intact.
There are some other workarounds here, but I think it’s a bit of throwing a sledgehammer at a nail compared to a very simple formula.