How do I open a .xlsx file without it dropping preceding zeros

[par72golf]

I received a file that has several columns and some of the columns contains data that can start with a zero. Problem is that when I open the Excel document, the zeros are dropped in those data cells. If the file came as a .CSV or .txt file, it wouldn't be a problem but that's not the case.

So, how can I open a .xlsx file without it dropping preceding zeros?

 

[Joe4]

Formatting is the answer then.

Maybe yes, maybe not.

It really depends on what you are using it for. If it is just a visual thing, Formatting will do the job. But if the data is being pulled/pass to another program that does NOT pull over the formatting, or you are using these values in certain calculations, it could be problematic.

See, Formatting does NOT actually change the contents of what is actually stored in the cell, it just changes the visual presentation of it.
For example, enter "1234" in cell A1 (as a number). Now, change the formatting of that cell to "00000" so that it now looks like "01234".

Now, in cell B1 enter the formula: =LEN(A1), which should return the length of the entry in cell A1.
You might expect it to return 5, but it actually returns 4 (because it formulas run against the against cell value, regardless of what formatting you have selected). So that "0" which is showing is not recognized because it really isn't there.

Just something to understand and be aware of. Like I said, if it is just the visual presentation you are after, you should be fine and Formatting will do the trick.

 

[Joe4]

We asked them to format this column as MM/DD/YYYY but we only see 3/2/2000 when it should be 03/03/2000. Any thoughts on that?

As long as the entry wasn't entered as Text, you can apply Custom Formatting to it. Just highlight the column and go to the Custom Formatting option like gebobs described, and enter mm/dd/yyyy in the Custom Formatting box.

 

[par72golf]

Maybe yes, maybe not.

It really depends on what you are using it for. If it is just a visual thing, Formatting will do the job. But if the data is being pulled/pass to another program that does NOT pull over the formatting, or you are using these values in certain calculations, it could be problematic.

See, Formatting does NOT actually change the contents of what is actually stored in the cell, it just changes the visual presentation of it.
For example, enter "1234" in cell A1 (as a number). Now, change the formatting of that cell to "00000" so that it now looks like "01234".
Now, in cell B1 enter the formula: =LEN(A1), which should return the length of the entry in cell A1.
You might expect it to return 5, but it actually returns 4 (because it formulas run against the against cell value, regardless of what formatting you have selected). So that "0" which is showing is not recognized because it really isn't there.

Oh crap, that will be a problem then. The data will be passed into another program/application. So what you're saying is even by doing that change to custom format, the data won't hold when passed into our other program?

I see what you're saying. I looked at the data again that I made the change to and it in formula field is still shows a 4 digit zipcode when the cell shows 5.

 

[par72golf]

Well, after all of this, I'm thinking we're going to have to find a 'Plan B'. Would be easy if the company would just send us a csv or txt file. Thanks for the responses guys!!

 

[gebobs]

That worked in order to see the zipcode with a preceding zero. What about a birthdate column? We asked them to format this column as MM/DD/YYYY but we only see 3/2/2000 when it should be 03/03/2000. Any thoughts on that?

Sure. Same thing, but look in the Date category. Find the format you want and off you go.

To be clear though, formatting only changes how the data are displayed, not the underlying value. If there is a reason other than conformity, aesthetics, personal preference, etc., you may have a problem.

For example, the date 3/2/2000, regardless how it is displayed, has a value of 36587, which is the serial number of the date where 1/1/1900 is 1. Time is merely a decimal of this so noon on 3/2/2000 is 36587.5. I probably didn't need to go off on this tangent, but its one of the aspects of Excel that fascinates me.

 

[Joe4]

Oh crap, that will be a problem then. The data will be passed into another program/application. So what you're saying is even by doing that change to custom format, the data won't hold when passed into our other program?

I think it depends on what the program is, and how it reads in the data (if it recognizes formatting or not). You might want to test it and see if it does, before jumping through hoops).

I have had to deal with issues like this, and I have created macros that will physically convert the values so leading zeroes exist. You can use the TEXT function to do that, i.e. =TEXT(A1,"00000") or =TEXT(A1,"mm/dd/yyyy"). Then you can use Copy -> Paste Special Values to convert those formulas to hard-coded values.

Note also that if you other program can read in CSV files, if you apply the formatting, if you then save the Excel file as a CSV file, it will keep the formatting (so the CSV will have leading zeroes!). Just don't make the mistake of using Excel to view the CSV file, as Excel will drop the leading zeroes when opening a CSV file. Use a Text Editor to view what actually appears in a CSV file.