How to Format Zip Codes in Google Sheets

In this tutorial article, I am going to talk about how one can format zip codes in Google Sheets.

Welcome to learning about the tips and tricks on working with Google Sheets! You can find helpful articles and tutorials on various topics; all of them are provided to make your experience with Google Sheets smooth and comfortable.

You can also learn how you can format phone numbers in Google Sheets from TeqTog

Let’s start!

Formatting zip codes in Google Sheets can be especially frustrating if they start with zero or zeros. Since Google Sheets tend to not consider any zero that is placed at the beginning of any value; typing zip codes in Google spreadsheets can be a little bit of a challenge.

There are a lot of areas whose zip codes start with one or many zeros; so I’m going to show, step-by-step, with an example; how you can format them in Google Sheets.

How to Format Zip Codes in Google Sheets: Step by Step Guideline

1. First thing you must do is select one cell or a range of cell. Now, these are the cells in which you are formatting the data for the zip codes, so make sure you know which cells to select from beforehand.

How to Format Zip Codes in Google Sheets

As you can see, I have selected the range of cells from B:2 to B:9 because that is where all my zip codes are given.

2. Now, the next step is to find the “Format” option from the menu bar at the top of your Google spreadsheet. From the drop-down menu, find the option “Numbers” and click on it. A second drop-down menu will roll down and, from there select the option “Custom Number Format”.

Go to Custom Number Format Option

3. A message box with an empty field right at the top, will be displayed where you have to give the following input:

  • You need to give one zero for every zero there is at the beginning of your zip code data (leading zeros). For instance, if we look at the first zip code from my example, there are a total of three zeros at the beginning of the zip code. Therefore, in the empty field, I will give three zeros.
  • After finishing giving input for the relevant zeros in the data, you need to now give the value “#” for each number other than zero that there is in your zip code. Again, if I take the first zip code from my data, I will need to give “##”, because, after all the zeros, there are two numbers given; “1 and 2”.
Click on Apply on Custom Number Format popup box after input the format code

4. Click on the “Apply” option given right next to the empty field in the box.

After you have been done with this process of formatting the relevant cells, your Google spreadsheet will no longer invalidate any zeros that will be at the beginning of your zip code data.

Things to Remember

  • Zip codes, despite having digits in them, are textual data and not numerical data.
  • You should check whether the length of your input for the format is the same length as the data, before clicking “Apply”.

Formatting Zip Codes using Plain Text

There is also an alternative approach to formatting zip codes that have leading zeros in them. All you need to do is, follow the same steps as shown in the first process till clicking on “Number” from the Format” menu. Now, from the drop-down menu go to “Plain Text” and click on it.


Last but not the least; you can also give an apostrophe before typing your zip code data. This will let the leading zeros appear in your data. For instance, for the zip code “00123” you will need to type ‘00123.

Final Thoughts

I hope this tutorial on how you can format zip codes in Google Sheets will help you when you try it for yourself.

Best of luck!

Read More Google Sheet Tutorials On:

Conditional Formatting Based On Another Cell in Google Sheets

How to Zoom-In and Zoom-Out in Google Sheets

How to Count Colored Cells In Google Sheets (Step-by-Step)

Google Sheets Count Cells with Specific Text

Related More Articles

Leave a Comment