How to display ZIP codes in Google Sheets?

by Joshua Dance   Last Updated January 10, 2018 16:03 PM

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?

Google search of Boston ZIP codes

Tags : google-sheets

Answers 4

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):


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

enter image description here

Joshua Dance
Joshua Dance
March 16, 2016 18:21 PM

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


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

Format -> Number -> Plain Text
March 16, 2016 20:15 PM

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

Custom number format

  1. Write 00000 in the entry box.

  2. Click the the Apply button.

March 17, 2016 01:20 AM

thank you! about friggin time someone figured this out

January 10, 2018 15:44 PM

Related Questions

Copying values between google sheets

Updated July 26, 2018 21:03 PM