Formatting ZIP codes in Google Sheets

Google Sheets is an incredibly powerful tool when it comes to managing your data, but like all spreadsheet software, it has a few quirks. When you’re working with locations on a spreadsheet, you may notice your ZIP codes in an odd format, for example, missing the leading zeros that make up the code. 

What can you do to fix these errors and make sure you can map your data accurately? In addition, how can you standardize your ZIP codes to be five digits rather than nine? In this article, we’ll talk you through how to do both.

Formatting US ZIP codes

If you have a spreadsheet with US ZIP codes, you’re likely seeing the leading zeros stripped for states like Maine and Connecticut or territories like Puerto Rico. This happens because Google Sheets automatically treats your field like a number, where a leading “0” is unneeded. This clearly isn’t true for a ZIP code, though. 

The issue can be easy to miss and annoying when you move on to creating a map using the data.

Luckily, there’s a fairly simple solution, which involves changing the format of your cells. Click Format in the top navigation of your Sheet, go to Number, and scroll down to the bottom of the menu to click Custom number format.

In the window that appears, put in either “00000” or “00000-0000” as your format, depending on the number of digits you have in the ZIP codes you’re dealing with. This allows you to standardize to one format. 

If you have a mix of five- and nine-digit codes, the next section shows you how to make your ZIP codes the same length.

And like magic, your cell will show all the digits rather than removing the leading zeros.

Converting Nine-Digit ZIP Codes to Five

The US has two ZIP code formats: the commonly seen five-digit ZIP code and the ZIP+4, also known as an extended ZIP code. These nine-digit ZIP codes include the same information as the five-digit codes, plus additional information about delivery routes in the area. 

If you’re dealing with a large number of addresses, chances are the ZIP codes will be a blend of five- and nine-digit numbers. Standardizing to the five-digit format is the easier approach, as you remove the extra four digits rather than expanding them to nine.

While this isn’t quite as simple as bringing back the leading zeros, it’s still straightforward. In your Sheet, add a column to the right of the one containing your ZIP codes by clicking Insert, then Columns, then Insert 1 column right.

In the cell to the right of your first row, add the following formula, making sure it specifies your cells: `=LEFT(A1,5)`.

Click the dot on the bottom right of your resulting cell and drag it down all your rows. This will automatically apply the formula to all selected cells in that column.

Make a Map!

Now that you’ve properly formatted ZIP codes in Google Sheets and fixed two common issues, you can take that data and map it using BatchGeo. If you’re using Excel, we got you covered — check out our article on Excel Zip Code Tricks.

Maps are the best way to visualize location data. Create one today.