Are you wondering how to write zip codes in google sheets? Especially when they have leading zeros in them? In this tutorial, I will show you how to format zip codes in google sheets.
Writing zip codes without leading zeros is simple. Google sheets will consider them as numeric values.
But consider the zip code 00001 which is for one of the areas in Alaska. Boston has zip codes like 02112, 02115, and so on.
When you write 00001 in a cell in google sheets, it will transform that to 1. Google sheets will remove any leading zeros that come before a number.
So how do you format such zip codes?
Let’s see how to write a zip code in google sheets that contains leading zeros and then numbers (eg: 001234).
Here are the steps to format zip codes:
1. Highlight one or a range of cells
2. Click on Format > Number, select Custom number format
3. In the input field type one zero for each leading zeros
4. Then type # for each number after leading zeros
5. Make sure total character length is equal to zipcode length
6. Click on apply
Now you can write the desired zip code in the newly formatted cells. Google sheet will not remove any leading zeros in the zip code.
Here is an video showing how to format zip codes in google sheets :
Let’s pause for a second and understand what we did here.
In reality, zip code is text data and not numeric data though it has numbers in it. This is because you do not add, subtract, multiply or divide zip codes. Zipcodes in some counties have letters in them, for example, Canada.
Since google sheets strip out the leading zeros in a number typed into a cell, you created a custom number format. You can call it a zip code format but technically it’s a custom format like many others.
Let’s see how this custom format works. Also, you will learn another way to write the zip code with leading zero in them.
Table of content
Format zip codes in google sheets using custom format
First, let’s see the steps to create the custom format, and then I will explain how the custom format logic works.
To write zip codes in google sheets with leading zeros,
1. Open google sheets on your computer and select the range of cells where you want to have custom formatting
2. From the top menu click on Format and then choose Number
3. Under the Number option, choose a Custom number format
4. This will open up the Custom number format window
5. Here in the input box, you need to type the custom format required for writing the zip codes and click Apply
I will write 000### and click on apply. This means you need a maximum of 3 leading zeros and 3 numbers after them. So the zipcode length is 6. If you need the zip code to have a maximum of 6 leading zeros then you will create the custom format by typing 6 zeros and then “#” for each number.
6. Now I can write the zip code 000123 in the cell and google sheet will not remove any zeros from the zip code
Explanation of the custom formats
I will list some sample formats and explain them one by one for practical understanding.
0000 – type this if you need to show four zeros before writing any number. For example, 0000111
Note: Four zeros custom format does not mean you have to use it only for writing a number that has 4 leading zeros. You can even write 0111 or 00111 or 000111. All would work fine
000-000 – This will help you write numbers like 0-0111 or 000-0001 and so on
0### – This format means you can write a maximum of one zero before writing numbers. “#” is for numbers.
If you apply this format to a cell and try to write 00111, then google Sheets will output as 0111. It will strip out the extra zero
0.00 – This format allows one to write a maximum one zero before the decimal. So you can write 0.123 or 1222.44 but 00.11 will transform to 0.11
So now you know how to write zip codes in google sheets with leading zeros using a custom format.
Note: The custom format is a Number format and not a plain text format. If you try the function ISNUMBER() on any of these cells, you will get the result as TRUE.
Let’s see another way to achieve a similar result.
How to format zip codes in google sheets as Text
The alternative way to write zip code with leading zeros is using Text formatting.
Here are the steps to add zipcodes as text field:
1. Select the cell where you want to write the zip code
2. Click on Format from the top menu and then click on Number
3. From the options available, choose Plain text
4. Now write zip codes with leading zeros in them
Now the entire field is a plain text field that will accept any number of leading zeros in the zip code. This is how you format zip codes in google sheets as text value.
Write zip codes with leading zeros using an apostrophe
A quick and easy way to allow leading zeros in the zip code is to add an apostrophe before it.
So instead of writing 00001, you will write ‘0001 in a cell.
Press enter and google sheets will transform ‘0001 to show 0001 in the cell.
Here is a quick animation.
FAQ
How to add leading zeros in front of zip codes in google sheets?
To add leading zeroes to a zip code, use custom number format. 0### means, you can have maximum one zero before a number in your cell.
Can I add zip code as a text in google sheets?
Yes, you can format cells as text fields. Then you can add zip codes with as many leading zeros you need.
Wrapping up
In this tutorial, you learned how to format zip codes in google sheets. You learned to create a custom format to write any type of zip code format.
You can use the same custom format method to create other formats for the custom data type. Go ahead and try creating some.
Appendix
[1] Custom number formatting in google sheets – Link
Further Reading
New to google sheets ? Start here
Learn Enter & Format data in google sheets:
