I have a gridview export to .csv file. When I export data, if there is a value like 10-10 and it open in excel it is shown as "10-oct". What do I do to get it to show up as "10-10"?
A:
Isn't that Excel just going "oh that looks like a date"?
Another application viewing the data may not try to interpret it as much.
Broam
2010-02-26 16:07:24
A:
Right click on the column in excel, click Format, and then set it to "text" instead of "general".
mgroves
2010-02-26 16:07:27
+1
A:
You can tell Excel the cell is TEXT and not a Date. Of course, doing this you will need to save it as XLS file.
Right Click > Format Cells > Under Number tab > Select Text instead.
Yada
2010-02-26 16:08:41
+2
A:
Though this has only been tested with Excel 2003 and could cause issues elsewhere, this worked for me:
1,3,10-10,a /* Displayed as 10-Oct */ 1,4, 10-10,b /* Displayed as 10-10 (note the leading space)*/ 1,3,10-10 ,a /* Displayed as 10-Oct */
Austin Salonen
2010-02-26 16:17:32
+1
A:
Similar to what Austin suggests, you can prepend the value with a single quote (') which escapes automatic formatting and treats the contests as TEXT:
1,3,'10-10,a
As noted in the comments though, this solution works in some programs, but not all versions of excel. If this is extremely important, you may need to consider exporting directly to .xls or some other rich format.
Dana the Sane
2010-02-26 16:20:25
I'll give you an upvote but with this note: on Excel 2003 this displayed the `'` and that may not be with the OP wants.
Austin Salonen
2010-02-26 16:52:46
Noted, I've updated my answer.
Dana the Sane
2010-02-26 19:03:03
A:
If you're going through the Import Wizard, you can tell Excel exactly how to interpret each column.
DaveE
2010-02-26 19:33:00
A:
If you change the data in the .csv file as follows ...,=”10-10”,... This will be displayed in Excel as ...,10-10,...
Excel will have kept the data as a formula, but copying the column and using paste special values will get rid of the formula but retain the formatting
Robert Mearns
2010-02-28 21:00:57