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.

Raymond Camden's Picture

About Raymond Camden

Raymond is a developer advocate looking for his next gig. He focuses on JavaScript, serverless and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support.

Lafayette, LA https://www.raymondcamden.com

Comments