Printable View
I have a CSV file which I am trying to open with Excel 2007. It was generated by a PHP script. In a column, I have dates in the format ddmmyyyy without separation. These dates can be performed in many formats, text, standard or date, whatever. My only problem is when I have a date with a 0 in front (eg 02112009) The 0 is not displayed, or it should be mandatory.
How do I get that 0 is displayed in the CSV file? FYI, when I open the file with notepad, the 0 is there.
It is quiet normal that you can see in NotePad but not in Excel simply because there are altogether different.
If you pass the date 01102009, Excel will remove the 0 "not significant" to the left, and then interpret the data as the number 1102009.
Possible solutions:
- Change the extension TXT: On opening the file using Excel, you can specify the date format of the column and Excel will interpret correctly your data.
- Open the file as it is and then treat it in Excel or VBA. For example, in Excel, you can have
Code:=DATE(RIGHT (A1,4);IF(LEN(A1)=7;MID(A1,2,2);MID (A1,3,2));IF(LEN(A1)=7;LEFT(A1,1);LEFT(A1,2)))
None of your two solutions work at my place. Is there a solution to this problem?
Also check that the date column, once recovered in Excel is in a format of dd/mm/yy or dd/mm/yyyy.
If it does not matter that the value of "rest" is in number format, you can pass in custom size: 0 #######
The "0" of the early forces the display of a figure in 8th position (counting from the right).