characters converted to dates when using write.csv

Question

my data frame has a column A with strings in character form

> df$A
[1] "2-60", "2-61", "2-62", "2-63" etc

I saved the table using write.csv, but when I open it with Excel column A appears formatted as date:

Feb-60
Feb-61
Feb-62
Feb-63
etc

Anyone knows what can I do to avoid this?

I tweaked the arguments of write.csv but nothing worked, and I can't seem to find an example in Stack Overflow that helps solve this problem.

Answer 1

Another solution - a bit tedious, Use Import Text File in Excel, click thru the dialog boxes and in Step 3 of 3 of the Text Import Wizard, you will have an option of setting the column data format, use "Text" for the column that has "2-60", "2-61", "2-62", "2-63". If you use General (the default), Excel tries to be smart and converts the answer for you.

Answer 2

As said in the comments, this is an excel behaviour, not R's. And that can't be deactivated:

Microsoft Excel is preprogrammed to make it easier to enter dates. For example, 12/2 changes to 2-Dec. This is very frustrating when you enter something that you don't want changed to a date. Unfortunately there is no way to turn this off. But there are ways to get around it.

Microsoft Office Article

The first suggested way around it according to the article is not helpful, because it relies on changing the cell formatting, but that's too late when you open the .csv file in excel (it's already converted to an integer representing the date).

There is, however, a useful tip:

If you only have a few numbers to enter, you can stop Excel from changing them into dates by entering:

• An apostrophe (‘) before you enter a number, such as ’11-53 or ‘1/47. The apostrophe isn’t displayed in the cell after you press Enter.

So you can make the data display as original by using

vec <- c("2-60", "2-61", "2-62", "2-63")
vec <- paste0("'", vec)

Just remember the values will still have the apostrophe if you read them again in R, so you might have to use

vec <- sub("'", "", vec)

This might not be ideal but at least it works.

One alternative is enclosing the text in =" ", as an excel formula, but that has the same end result and uses more characters.