Excel formats a String to Date in a CSV file. How to retain the original string value

microsoft-excel-2010

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 gVim

Sample CSV file opened in Microsoft Excel 2010

However, when I open the CSV file in Excel, it formats october51 to Oct-51.

CSV file opened in Excel, october51 has been formatted as 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

enter image description here

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.

How can I make Excel retain the cell's original string value?

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:

enter image description here

enter image description here

Un-tick Tab and tick Comma box:

enter image description here

Choose 'Text' button and click 'Finish' button.

enter image description here

Hope this helps.