Stop Excel from automatically converting certain text values to dates

Question

I have found that putting an '=' before the double quotes will accomplish what you want. It forces the data to be text.

eg. ="2008-10-03",="more text"

EDIT (according to other posts): because of the Excel 2007 bug noted by Jeffiekins one should use the solution proposed by Andrew: "=""2008-10-03"""

I know this is an old question, but the problem is not going away soon. CSV files are easy to generate from most programming languages, rather small, human-readable in a crunch with a plain text editor, and ubiquitous.

The problem is not only with dates in text fields, but anything numeric also gets converted from text to numbers. A couple of examples where this is problematic:

• ZIP/postal codes
• telephone numbers
• government ID numbers

which sometimes can start with one or more zeroes (0), which get thrown away when converted to numeric. Or the value contains characters that can be confused with mathematical operators (as in dates: /, -).

Two cases that I can think of that the "prepending =" solution, as mentioned previously, might not be ideal is

• where the file might be imported into a program other than MS Excel (MS Word's Mail Merge function comes to mind),
• where human-readability might be important.

My hack to work around this

If one pre/appends a non-numeric and/or non-date character in the value, the value will be recognized as text and not converted. A non-printing character would be good as it will not alter the displayed value. However, the plain old space character (\s, ASCII 32) doesn't work for this as it gets chopped off by Excel and then the value still gets converted. But there are various other printing and non-printing space characters that will work well. The easiest however is to append (add after) the simple tab character (\t, ASCII 9).

Benefits of this approach:

• Available from keyboard or with an easy-to-remember ASCII code (9),
• It doesn't bother the importation,
• Normally does not bother Mail Merge results (depending on the template layout - but normally it just adds a wide space at the end of a line). (If this is however a problem, look at other characters e.g. the zero-width space (ZWSP, Unicode U+200B)
• is not a big hindrance when viewing the CSV in Notepad (etc),
• and could be removed by find/replace in Excel (or Notepad etc).
• You don't need to import the CSV, but can simply double-click to open the CSV in Excel.

If there's a reason you don't want to use the tab, look in an Unicode table for something else suitable.

Another option

might be to generate XML files, for which a certain format also is accepted for import by newer MS Excel versions, and which allows a lot more options similar to .XLS format, but I don't have experience with this.

So there are various options. Depending on your requirements/application, one might be better than another.

---

Addition

It needs to be said that newer versions (2013+) of MS Excel don't open the CSV in spreadsheet format any more - one more speedbump in one's workflow making Excel less useful... At least, instructions exist for getting around it. See e.g. this Stackoverflow: How to correctly display .csv files within Excel 2013? .

Working off of Jarod's solution and the issue brought up by Jeffiekins, you could modify

"May 16, 2011"

to

"=""May 16, 2011"""

I had a similar problem and this is the workaround that helped me without having to edit the csv file contents:

If you have the flexibility to name the file something other than ".csv", you can name it with a ".txt" extension, such as "Myfile.txt" or "Myfile.csv.txt". Then when you open it in Excel (not by drag and drop, but using File->Open or the Most Recently Used files list), Excel will provide you with a "Text Import Wizard".

In the first page of the wizard, choose "Delimited" for the file type.

In the second page of the wizard choose "," as the delimiter and also choose the text qualifier if you have surrounded your values by quotes

In the third page, select every column individually and assign each the type "Text" instead of "General" to stop Excel from messing with your data.

Hope this helps you or someone with a similar problem!

WARNING: Excel '07 (at least) has a(nother) bug: if there's a comma in the contents of a field, it doesn't parse the ="field, contents" correctly, but rather puts everything after the comma into the following field, regardless of the quotation marks.

The only workaround I've found that works is to eliminate the = when the field contents include a comma.

This may mean that there are some fields that are impossible to represent exactly "right" in Excel, but by now I trust no-one is too surprised.

2018

The only proper solution that worked for me (and also without modifying the CSV).

Excel 2010:

1. Create new workbook
2. Data > From Text > Select your CSV file
3. In the popup, choose "Delimited" radio button, then click "Next >"
4. Delimiters checkboxes: tick only "Comma" and uncheck the other options, then click "Next >"
5. In the "Data preview", scroll to the far right, then hold shift and click on the last column (this will select all columns). Now in the "Column data format" select the radio button "Text", then click "Finish".

Excel office365: (client version)

1. Create new workbook
2. Data > From Text/CSV > Select your CSV file
3. Data type detection > do not detect

Note: Excel office365 (web version), as I'm writing this, you will not be able to do that.