Leading 0s and Excel

So, one of my clients has a few Excel reports that are generated on the fly. They pointed out that leading zeroes were being dropped. This was easy enough to fix in the Excel sheet, but I needed a solution that would work out of the box.

When I searched Google, I found about a million results, of which 99% told me how to fix it by formatting the columns. This wouldn't work.

I then saw multiple folks suggest putting a single quote in front of the data. While this fixed the issue, it also showed up in the Excel report, despite claims that it would not. (Or perhaps this worked in earlier versions of Excel.)

Eventually I found a solution. If you output the zip as

="#theZip#"

It works correctly. Maybe = replaced ' in the latest version of Excel.

Like This?

If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can also subscribe to the email feed to get notified of new posts.