How to display ZIP codes in Google Sheets?

Question

Boston has leading zeros for its ZIP codes.

Google Sheets is very helpful in removing leading zeros but unfortunately it removes them from Boston ZIP codes.

I tried to find a number format for address ZIP codes but couldn't find one. I also tried to find a number format that preserves leading zeros but couldn't figure it out.

How can I prevent Google Sheets from removing the leading zero in ZIP codes?

Answer 1

1. Select the range to apply the Zip code format
2. Go to Format > Number > More Formats > Custom number format...

3. Write 00000 in the entry box.

4. Click the the Apply button.

Answer 2

You can also just put an apostrophe in front of the zip codes like

'02108

Or select the column / cells that will contain the zip codes and set to the following as listed above

Format -> Number -> Plain Text

Answer 3

Format>Number>Plain Text works great to allow leading zeros.

To restore the leading zeros if they are gone without having to add them manually, I found this article.

Basically you can use a formula to check the length of your zip code column. If the length is 4, it adds a leading 0.

The formula (assuming your zip code is in column B):

=if(len(B2)=4,"0"&B2,B2)

Make sure to format the resulting column as plain text as well.