0

I am trying to upload a CSV file and importing it to Google Spreadsheets. The file contains the following:

hi,hello\r\nwelcome,to\r\npeaceful,world

What I expect in the Google spreadsheet is:

hi,hello
welcome,to
peaceful,world

But it is imported as a single line separated by comma.

In other words, what is Google Spreadsheets’ new line character?

4
  • Why not do actual carriage returns (CHR(13)) and or line feed characters (CHR(10))? I've never worked with a CSV file that used \r to put in newlines. – ale Oct 2 '14 at 0:20
  • @AlE. the file content is created using XSLT transformation, since it is a text content I couldn't use <br/> tag. – Senduran Oct 2 '14 at 4:00
  • @AlE. You mean CHAR(13) and CHAR(10) I guess? – Jacob Jan Tuinstra Oct 2 '14 at 18:52
  • 1
    @Jacob: sure. Different languages use different syntax. – ale Oct 2 '14 at 22:05
1

I acknowledge that this does not answer "what is Google Spreadsheets’ new line character?".

However, if not for OP for others wanting to get from a text file containing:

hi,hello\r\nwelcome,to\r\npeaceful,world

to :

WA67626 example

a process of the kind below may suit.

The technique is basically to 'feed' Google Sheets with a replacement for \r\n that it recognises as time to move into the next cell.

The easiest way for this may be to open the text file in Word, replace \r\n with ^p and copy/paste the result into Google Sheets.

However, Word could be an expensive solution if required only for this purpose and there are free alternatives. One of the most popular of which is Notepad++.

Notepad++ recognises \r\n as a CR LF pair (carriage return, line feed) but only when Search Mode is Extended. In that mode Find what: \r\n in the text file will not find the character to be replaced (since as we know, as text, that combination is not CR LF - or none of this would be necessary).

So an extra step is necessary. First, in Search Mode Normal, Find what: \r\n and Replace with: something not elsewhere present (pipe | is often suitable for this).

Another possibility but perhaps only preferable for a limited requirement is to accept entry as hi,hello\r\nwelcome,to\r\npeaceful,world into a single cell (say A1) and then:

=transpose(split(A1,"\r\n",0))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.