How to import CSV with line breaks in cells and numbers in text?

Copper Contributor

I have a CSV file with 1 data cell containing line breaks (enclosed in "...") and another data cell containing e.g. "5.10".

 

1. When I "just open" this CSV file in Excel (e.g. by double clicking in Windows Explorer), then the data cell containing line breaks is interpreted correctly (according to https://tools.ietf.org/html/rfc4180), however the cell with "5.10" is interpreted as date "05.10.2018". Formatting this cell as text does not help but yields "43378" (probably the internal representation of the given date).

 

2. On the other hand, when I import the file in Excel as text file (either by renaming it into "<name>.txt" or by [Data -> Import from text], I don' know the exact English names of the menu options; I got the German version), then I have the possibility to mark the column containing "5.10" as text, resulting this cell correctly imported as "5.10", however, then the line breaks in the data cell with line breaks are treated as line breaks between data rows (resulting in each line break starting a new row in the Excel sheet).

 

Is there any way to combine these two options, meaning treating the file as CSV file with respect to respecting line breaks in data cells AND telling Excel that the column/cell containing "5.10" is not date but text?

 

Or, alternatively, is there any way to convert the date cell back to a text cell and receiving "5.10" instead of "43378"?

 

Having " 5.10" (with a blank before the first digit) solves the problem. But is there a solution without this blank (which would be present in the resulting Excel file)?

0 Replies