Leading With A Zero

    Formatting A Leading Zero In A Spreadsheet


    If you have ever tried to format a zip code so that the leading zero didn't disappear every time you typed it in, you know the frustration! There are usually no drop down menu items that address this issue - no matter what system you are using. It shouldn't be this difficult. This is a basic piece of data we collect in everyday business life.

    In the United States, if you live in states further to the west, you may not even realize what I am talking about. Here in New England, our zip codes start with a zero. Typically, when you type the zip code into a spreadsheet, the number will be truncated and the  leading zero dropped. It's enough to make you pull your hair out! This becomes a nightmare when you are trying to address anything postal or trying to sort data by zip codes.

    The designers of the spreadsheets are slowly coming around. The only option, until they give us a menu item, is a long, drawn out way to make the zero "sit and stay!". Each system is different, but similar. I am going to give you the secret for Google Sheets since this is a universal free system many people use.

    1. Highlight the cell/column/row where the zip codes are going to live.
    2. On the menu bar at the top, chose Format > Number > 00000. This will set up the selected cells to a 5 digit zip code with leading zeros. This works for the 9-digit zip code as well.

    In some other spreadsheets, you may have to go further. Select Number or Cell and then see if there is a General Code or Format Code where you can type in '00000'. These are fairly well hidden. You may have to hunt a little, but they are there.

    You're welcome!

    If you have other spreadsheet formatting questions, maybe I can help...before all your hair is gone!

    If you found this article helpful, feel free to share it with others...


  • (no comments)

Website Created & Hosted by Doteasy Web Hosting Canada