6

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

8
  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.

3

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
2

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.

enter image description here

1
  • While the method in the referred page works several parts of that page are obsolete. See webapps.stackexchange.com/a/90982/88163 for the method that the referred page says that it's not avaible for the Google spreadsheets.
    – Rubén
    Mar 17 '16 at 1:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.