Stop Excel from automatically converting certain t

Question

Does anyone happen to know if there is a token I can add to my csv for a certain field so Excel doesn't try to convert it to a date?

I'm trying to write a .csv file from my application and one of the values happens to look enough like a date that Excel is automatically converting it from text to a date. I've tried putting all of my text fields (including the one that looks like a date) within double quotes, but that has no effect.

Answer 1

Still an issue in Microsoft Office 2016 release, rather disturbing for those of us working with gene names such as MARC1, MARCH1, SEPT1 etc. The solution I've found to be the most practical after generating a ".csv" file in R, that will then be opened/shared with Excel users:

1. Open the CSV file as text (notepad)
2. Copy it (ctrl+a, ctrl+c).
3. Paste it in a new excel sheet -it will all paste in one column as long text strings.
4. Choose/select this column.
5. Go to Data- "Text to columns...", on the window opened choose "delimited" (next). Check that "comma" is marked (marking it will already show the separation of the data to columns below) (next), in this window you can choose the column you want and mark it as text (instead of general) (Finish).

HTH

Answer 2

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"""

Answer 3

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.

Answer 4

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.

Answer 5

Okay found a simple way to do this in Excel 2003 through 2007. Open a blank xls workbook. Then go to Data menu, import external data. Select your csv file. Go through the wizard and then in "column data format" select any column that needs to be forced to "text". This will import that entire column as a text format preventing Excel from trying to treat any specific cells as a date.

Answer 6

This issue is still present in Mac Office 2011 and Office 2013, I cannot prevent it happening. It seems such a basic thing.

In my case I had values such as "1 - 2" & "7 - 12" within the CSV enclosed correctly within inverted commas, this automatically converts to a date within excel, if you try subsequently convert it to just plain text you would get a number representation of the date such as 43768. Additionally it reformats large numbers found in barcodes and EAN numbers to 123E+ numbers again which cannot be converted back.

I have found that Google Drive's Google Sheets doesnt convert the numbers to dates. The barcodes do have commas in them every 3 characters but these are easily removed. It handles CSVs really well especially when dealing with MAC / Windows CSVs.

Might save someone sometime.