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?

 

[gebobs]

Did the data appear that way as sent to you? It sounds like a formatting issue but I don't see why the formatting would be lost.

At any rate, you may be able to set it straight.

Are the data in a column supposed to have a consistent number of digits? e.g. 00231, 23456, 00002, etc? If so, you can create a custom format "00000".

 

[Joe4]

Welcome to the Board!

How do you know it is dropping leading zeroes (that is, how do you know that there were leading zeroes in there to begin with)?
If it is truly an Excel file, it shouldn't do any conversion to the data, it should open it exactly as it is.

I question whether or not the file is really an Excel file. I have seen CSV files saved with Excel file extensions. The easiest way to tell is to try to open the file with NotePad or WordPad. If you are able to do that and it looks like a regular text file, then it is not really an Excel file. If it is truly an Excel file, you will see all sort of junk (it will be pretty much unreadable).

 

[par72golf]

Did the data appear that way as sent to you? It sounds like a formatting issue but I don't see why the formatting would be lost.

At any rate, you may be able to set it straight.

Are the data in a column supposed to have a consistent number of digits? e.g. 00231, 23456, 00002, etc? If so, you can create a custom format "00000".

Yes, the data file came to us as an .xlsx file. One of the fields that really sticks out is zipcode and birthdate. Some zipcode fields only have 4 numbers in (1234 rather than 01234), and we asked them to format the birthday field as 01/02/15 but when I open the file it shows as 1/2/15.

For the last thing you mentioned, I'm not sure I follow what you're saying. The file already contains data, I'm not creating or adding any data to the file.

 

[par72golf]

Welcome to the Board!

How do you know it is dropping leading zeroes (that is, how do you know that there were leading zeroes in there to begin with)?
If it is truly an Excel file, it shouldn't do any conversion to the data, it should open it exactly as it is.

I question whether or not the file is really an Excel file. I have seen CSV files saved with Excel file extensions. The easiest way to tell is to try to open the file with NotePad or WordPad. If you are able to do that and it looks like a regular text file, then it is not really an Excel file. If it is truly an Excel file, you will see all sort of junk (it will be pretty much unreadable).

Yes, I have tried opening the file in NotePad & TextPad and it just returns a bunch of garbage.

 

[Joe4]

I'll go back to the first question I posed:

How do you know it is dropping leading zeroes (that is, how do you know that there were leading zeroes in there to begin with)?

If you use Excel to open the file, how can you tell that the file actually DID originally have leading zeroes and that they were dropped in the opening of the file? I am betting that the those fields were like that in Excel from the get-go.

 

[gebobs]

Yes, the data file came to us as an .xlsx file. One of the fields that really sticks out is zipcode and birthdate. Some zipcode fields only have 4 numbers in (1234 rather than 01234), and we asked them to format the birthday field as 01/02/15 but when I open the file it shows as 1/2/15.

For the last thing you mentioned, I'm not sure I follow what you're saying. The file already contains data, I'm not creating or adding any data to the file.

The data are stored as a number. Excel does not see preceding zeroes and only shows them if you specify as such.

On your Home toolbar, click on the little arrow thingee at Number. For Category, select Custom. In the box below Type, enter 00000. Or you can even select the Special category and there are canned formats for Zip code, Zip+4, phone number and SSN.

 

[par72golf]

I'll go back to the first question I posed:

If you use Excel to open the file, how can you tell that the file actually DID originally have leading zeroes and that they were dropped in the opening of the file? I am betting that the those fields were like that in Excel from the get-go.

Yeah, I agree. I said the same thing to a co-worker after I replied to your post. We don't know that the zeros are really there since we can't open the file with any other program.

 

[gebobs]

Yeah, I agree. I said the same thing to a co-worker after I replied to your post. We don't know that the zeros are really there since we can't open the file with any other program.

Formatting is the answer then.

 

[par72golf]

The data are stored as a number. Excel does not see preceding zeroes and only shows them if you specify as such.

On your Home toolbar, click on the little arrow thingee at Number. For Category, select Custom. In the box below Type, enter 00000. Or you can even select the Special category and there are canned formats for Zip code, Zip+4, phone number and SSN.

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?