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

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

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

'02108

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

Format -> Number -> Plain Text
ode2k
ode2k
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.

Rubén
Rubén
March 17, 2016 01:20 AM

thank you! about friggin time someone figured this out

nick
nick
January 10, 2018 15:44 PM

Related Questions




How to get the data value of NOW() and stored as text

Updated December 22, 2017 17:03 PM