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.
Judi Sohn 
13 responses to “Tip: How to add leading zero for zip codes in Google Docs”
I found that formatting the column on the Excel spreadsheet as text, then uploading into Google Docs preserved the leading zero. (Excel 2007 and Google Chrome)We have the same problem in the UK with telephone numbers as all the area codes begin with a zero.
Thanks for the comment. If the Excel spreadsheet has zip codes that are really 4 characters and formatted as zip codes they will import to Google without the special formatting and the zeroes have to be added. You could add them with a formula in Excel too.
For Google docs select the cell/row and go to Format-> Number and select “Plain Text”. Now add the number starting with zero Eg: 012345 it will never ignore zero now it will show as is.
Yes, but if you have a spreadsheet with 1000 rows, manually adding all those zeroes is not fun. That’s why I suggested the formula which can be quickly copied down.
Also the problem with this approach is when dragging 00001 and 00002 to create a list of numbers like 00003 00004 etc it puts a comma like 0,0003 if plain text is used. Most annoying.
JayaChandra’s advice above is a far simpler solution for displaying leading zeros for East Coast Zip codes and other numbers starting with 0 (for example Taiwanese phone numbers) in Google Docs / Google Spreadsheets.
The “Plain Text” option is redundantly available (and more quickly, since not in a sub-menu) at the bottom of Google Spreadsheets’ “123” drop-down menu. To best use the feature, first select an entire column or more by clicking the column heading (e.g., “A”, “B”) and then choose “Plain Text” from the drop-down.
If the numbers were entered with the leading zeros, then the zeros might reappear after you apply the “Plain Text” formatting. I’ve seen it go both ways in the current rev of Google Spreadsheets, so I think this is a buggy area in the app. I believe that if any of the entered characters in the column were non-numeric, then the zeros won’t reappear.
thanks, jaya. this is exactly what i needed to know.
THis is very basic but how do you just add the zero to a regular Apple doc….one that currently exists in apple. thx
Do you mean Apple Numbers? I honestly have no idea. I don’t use that application. Sorry.
yes….thanks anyone..seems to be the simplest thing….but can’t figure it out..
thx anyway I meant to write
random little bit, but the quotation marks in the above formula are glitchy, so when copying and pasting, use this: =IF(LEN(I2)=4,”0″&I2,I2)
Thanks, Judi AND LeilaZoom- I was getting an error message but once I retyped the quotes everything worked great!