Format CSV to Force Excel to Interpret Value as String

csvformattingmicrosoft excelstringtext formatting

I've been searching around for a while trying to figure out how to output a CSV file in such a way to force Excel to interpret the values as a string and not try to convert them to numbers or dates.

e.g.:

"141", "10/11/2002", "350.00", "1311742251"

Excel tries to "intelligently" convert all these to its native date/number formats. Is there a way around that?

EDIT: Clarified the intent of my question, sorry for confusion.

Best Answer

For those that have control over the source data, apparently Excel will auto-detect the format of a CSV field unless the CSV column is in this format:

"=""Data Here"""

eg...

20,       5.5%,      "0404 123 351", "3-6",  "=""123"""
[number]  [percent]  [number]        [date]  [string]  <-- how Excel interprets

It also works in Google Spreadsheet, but not sure if other spreadsheet apps support this notation.

If you suspect any the data may contain quotes itself, you need to double-escape them, like this...

"=""She said """"Hello"""" to him"""



(EDIT: Updated with corrections, thanks DMA57361!)

Related Question