Copy Data into MS-Excel

One of my application exports data into Excel format. The data in one of the column looks like '12/12/12', '12.5/13.9/12' and '12,12'. The problem I am facing is, excel coverts this data to DATE formate. and it makes '12/12/12' to '12/12/2012'. I tried to change the format of column to TEXT (right click on selected column and Format Cells... option), then it converts '12/12/2012' to '41255'.

Can someone please give me some idea to keep开发者_Go百科 the format of data same in excel as it was exported (it should keep '12/12/12' as '12/12/12' not '12/12/2012' or '41255')???

Thanks

I'm not sure of the exact steps you are going through (and that really matters here) so I'll give you the big picture and hopefully that will help.

Excel stores a datetime as the number of days since 1/1/1900 (or 1/1/1904) where the fraction part is the time of day. So for a datetime you have a formatting pattern and a number. If that cell is then changed to another format, the value is the number 41255, not the formatted displayed string.

The other half of what you are hitting is if Excel is given a string that it can parse as a date, it will do so changing it from a string to the number & datetime format. Think of Excel as a very loosly typed system that constantly tries to convert cell values into a number and format. (This is also a problem with zip codes that start with 0 and many other cases.)

So... What you need to do is force the content to be a text string. Generally you can do this by giving it the value '09/29/55 where the leading ' says this is text - don't change it.

If this isn't enough help, give very detailed steps of what you're doing and I'll try to figure out which step is changing it on you.