While working on a spreadsheet the other day I had difficulty getting zeros to show in a column of zip codes even though the numbers were formatted as zip codes. I’d never had this problem before, but it was somebody else’s worksheet.
There are several ways to approach this problem.
- You can try formatting the numbers as text. That didn’t work for me.
- You can type an apostrophe before each of the numbers to basically store the number as text. That didn’t work for me either, but there may have been other formatting or changes made to this worksheet that I wasn’t aware of. I tried clearing the formats of the numbers and I still couldn’t get the zeros to show.
- The one method that DID WORK for me was using the TEXT function: TEXT(value,format text) where value is the number I wanted to format, and format text is the formatting I wanted to apply to the number. Doing this without defining the number of placeholders you have in the zip code column will probably result in the same problems as before. So, you have to use placeholders in the function. Five zero placeholders makes sure that in a five digit zip code (like in the US), if there are any leading zeros, they will appear before the rest of the non-zero numbers.
=TEXT(C1,”00000″)
It seems like an easy fix, but I would have never thought of it myself. It could have been something wonky in my spreadsheet or this could be something wonky in Excel. We’ll never know. But the TEXT function did work in this case.
NOTE: You’re applying this function to the original set of numbers that wouldn’t display correctly. This involves inserting a new column which I would label Zip Code Fix. You can’t delete the old column or the new column’s function results will disappear. Just keep in mind what you’re doing here when you go to print or merge the data.
