Let the following vectors:
x <- c(123456789123456789, 987654321987654321)
y <- as.character(x)
I'm trying to export
y into a csv file for later conversion into XLSX (don't ask, client requested), like so:
write.csv(y, file = 'y.csv', row.names = F)
If I open
y.csv in a pure word processor, I can see it has correctly inserted quotes around the elements, but when I open it in Excel the program insists in converting the column into numbers and showing the contents in scientific format. This requires the extra step of reformatting the column, which can be a real time-waster when one works with lots of files.
How can I format a character vector of 20-digit numbers in R so that Excel doesn't display them in scientific notation?
Instead of opening the csv file via
File->Open, you can go to
Data->From Text in Excel and on the last step specify the
Column data format to be
Not really sure though that you save any time by doing this, you can also consider using the
WriteXLS (or some other direct to xls) package.
Here's a much better way of forcing Excel to read as text:
write.csv(paste0('"=""', y, '"""'), 'y.csv', row.names = F, quote = F)
In Excel, select the column of numbers, and format them as text. (Format Cells -> Number tab -> Text in the list on the left)