Import multi-line cells in CSV format into Google Sheets

In Google Sheets (and Excel or Mac Numbers should be similar for most of this), you normally enter a cell's value by hitting enter which moves to the next cell. But if you need multiple lines in a single cell, you need to know the trick of holding command/Apple-key when pressing enter.

If you want to import a CSV file into Google Sheets but get the same effect, then you need to escape the new-line markers.

First, a cell which contains more than one line (instead of each line being on a new row) needs to be "escaped" with double quotes at the start and end of the cell's value. Next, your file needs to use the "carriage return" (CR) character to demarcate new rows instead of just a newline (LF - line feed). In most programming languages, a new line ends with the "\n" special character. But for the format we want, we need only use "\n" for separating lines within a cell. Then we need to use the "\r" carriage return character to separate rows.

Example:

apple,"From time to time
The clouds give rest
To the moon-beholders."\r
potatoe,"A single line"

You can verify the format which Google Sheets expects for a CSV import by creating a CSV export from Google Sheets, and having cells with multiple lines in it. Then open the resulting file in a code or text editor which can display DOS file endings (carriage returns).

Add new comment