I'm encountering a problem in Microsoft Excel 2010 regarding auto-formatting a String into a Date, and it seems to lose the cell's original value.
Steps to reproduce:
1 Create the following CSV file:
username, points
phil, 1
october51, 5
2 Open the file in Microsoft Excel 2010
Note: There is a row with the username october51
. I would like Excel to retain this value instead of converting it to some other value.
Sample CSV file opened in a text editor
When I open this sample CSV file in a text editor (eg: gVim), I see exactly what I expect:
Sample CSV file opened in Microsoft Excel 2010
However, when I open the CSV file in Excel, it formats october51
to Oct-51
.
I understand why Excel is trying to format the field into a more readable format, but I'd rather keep the value as october51
since it's someone's username.
Trying different cell formats in Excel
Notice that when the file is first opened, the field format for the Oct-51
cell is Custom. So, I tried changing the format to Text
This is no better. The value of the cell is 18902
, instead of october51
which is what I was expecting. It looks like Excel throws away the cell's original value immediately upon opening the file.
I've tried every possible format (eg: General, Text, Special, Custom), and none of them output the value october51
.
The true issue is that this cell formatting is persisted when the file is saved. If I save this file in Excel and reopen it in a text-editor, the username october51
has actually been changed to Oct-51
or 18902
, depending on which format was chosen for the cell.
Best Answer
Try the below steps:
Rename the CSV file to have an extension of TXT (ie: sample.txt). Open a blank Excel sheet > go to 'File' > Open > In the Open search window, navigate the .csv file by choosing "Text Files (*.txt, *.csv)" from the drop down list > click on 'Open' but now. Refer the below step by step screenshots:
Un-tick Tab and tick Comma box:
Choose 'Text' button and click 'Finish' button.
Hope this helps.