Working with Zip Code Format in Google Sheets

Are you working with zip code data in your Google Sheet? If you’ve imported some 5 digit zip code data and lost the leading zeros from certain states like Connecticut, Massachusetts, Puerto Rico, and New Jersey, I’ll show you several easy ways to put it back. If you need to import zip code data and stop your sheet from removing the leading zeros, I’ll show you how to do that too.

Adding the leading zero back to zip codes

States like Puerto Rico have 0 (zero) as the first digit in their zip code. San Juan (PR) has a zip code of 00926, which has two leading zeros. Other states like Maine also have 0 as their first digit. But most of the zip locations in Maine, like Bangor (zip code 04401) only have one leading zero. This means, if you’ve already run an import, you may well have data that looks like the image below.

How do we add back the zeros to San Juan and Bangor? There are 2 ways, and which one you use depends on exactly what you want to accomplish.

Do you simply want to display the missing zero or do you want to put it back into the cell value?

Using a custom number format to show missing zeros in zip codes

To add back zeros to 5 digit zip codes, you need a custom number format of 00000. Formatting the zip code column (A) in our example above using this number format will display the data like this:

To add a custom number format, you need to first select the column, or cells containing your zip codes and then open the Format > Number > Custom number format option on the menu.

And then enter 00000 into the custom number format box and click apply.

Your cells should now display leading zeros and all contain 5 digits.

Using 0 in a custom number format will display a zero in its place if there is no significant digit in the number’s value. So 1234 would display as 01234.

Custom number formats

0 - A digit in the number. An insignificant 0 will appear in the results.

Google Sheets online help

It’s important to note here, that this will only affect how the cells display. The actual values in the cells will still be numbers and NOT have leading zeros.

Using TEXT in a formula to add back zip code zeros

If you want to fix the data in your zip code column, the best option is to add a new column and use the TEXT function. Below is a screen grab of how that looks.

TEXT(number, format)

Converts a number into text according to a specified format.

Google sheets online help

TEXT accepts 2 parameters, firstly a number to be formatted, and secondly the custom number format string to format it with. It will return a number, converted into text using the given number format.

We won’t cover number formats in depth here, but to create a number with 5 digits that will display any missing digits as zeros, the format is simply “00000“.

Here’s a step by step to get your leading zeros back by adding a new column, fixing the data and copying it back:

1. Insert a new column in your sheet next to the zip column
2. Paste the formula =TEXT(A2,"00000") into the new empty cell next to the top zip code cell with missing leading zeros
3. Edit the formula, replace A2 with the location of the top cell in your sheet with missing leading zeros (this is A2 in our example above)
4. The first zip code cell in your new column should now have the correct number of leading zeros
5. Copy this formula to all other cells in the new column with missing leading zeros
6. Now select the new column and copy Ctrl+c (⌘+c on Mac)
7. Paste the fixed data back over your original data (paste values only) Ctrl+Shift+v (⌘+Shift+v on Mac)
8. Delete the newly added column

Importing zip code data (without losing zeros)

If your zip code data comes from a csv file that already contains leading zeros, and you don’t want to lose them during an import, you need to make sure you don’t tick the ‘Convert to numbers, dates and formulas‘ option when importing. This works if all you care about is the zip code data. But it may break other data fields in your import.

When you import without this option checked, sheets will keep your leading zeros and enter the zip codes into your sheet as text cells by appending a single quote ( ‘ ) in front of them. As in this image below.

The problem with this approach is that it has also added single quotes to our lat and long columns, and these are clearly supposed to be numbers. It is easy to fix this by selecting those columns in the sheet and formatting them using Format > Number > Number. But is there a way to around having to do this?

I can’t find a simple one. If you format your destination columns and then import and append to current sheet, all that happens is the column formatting gets overwritten. Therefore, I only see 2 simple options.

1. Import with the Convert to numbers option OFF and apply number formatting to number columns
2. Import with Convert to numbers ON and use the method shown above to add leading zeros back to the data

Until Google sheets has a more advanced import feature that allows you to select exactly what you want to do with each column (like Excel), then you’re stuck with these 2 options.