How to prevent automatic truncation of leading zeros in Excel cell

If you are using MS Excel to edit the cells where you need to store the numbers with leading zero, just format and define the cell content as TEXT. If you are storing it programmatically, you can put the value in a quote '' for the same.

Update

I just noticed that if I go to "Format Cells" and select custom format and put, say 0000 to the format, any number that I put in the cell, it is is less than 4 digits, it still maintain the 4 digit length with the leading '0', like 0001, 0099, 0120, 9999

Click the cell and On the Home tab, in the Number group, click the dialog box launcher Button image next to Number.

In the Category list, click Custom and then, in the Type box, type the number format, such as 000-00-0000 for a social security number code, or 00000 for a five-digit postal code.

https://support.office.com/en-us/article/Keep-leading-zeros-in-number-codes-1bf7b935-36e1-4985-842f-5dfa51f85fe7

You can also just prefix your value with a ', though this flags the cell until you make it a text field.

I thought it might be helpful to do a step by step for some people that might need a little more help finding where to select these things.

1. Select the square or squares you need by clicking on them/highlighting them.

2. Stay on the Home tab up at the top. All the way on the right side, on the second box to the right, there is an option that says Format. Click on that.

3. Click on Format Cells... all the way at the bottom. The first tab should say Number. On that Number tab, there's an option that says Text. Click on it. Now, press OK at the bottom, and done.

Many have stated above that this is not an easy maneuver. Here is a suggestion.

Say your erroneous zip codes (no leading 0's) are in column A.

1. Insert a dummy column, let's use B.
2. In column B put formula, =A1*1 and copy down to capture all zips. After you do this you should see leading zeros, however you are not finished. They will still not be read.
3. Copy column B, then paste the contents of column B to a new workbook.
4. Save the new workbook as a .csv file (when saving click on the "Save as Type" box and select CSV comma delimited).
5. Open that new CSV file you just created.
6. Copy, then paste the contents directly over your crappy incorrectly formatted zip codes. (this means you can't have your paste location filtered, keep that in mind). The column contents must match or your data will be mismatched.
7. Click and select all the zip codes you just pasted.
8. Select format cells and choose: Special, then Zip Code
9. Hopefully your zips will hold to 5 digit formatting and should be able to be read in formulas and such.

It's a long fix and has worked for me. Hope it works for you!