How to replace the line breaks in a cell by explicit "\n"?

How to replace the line breaks in a cell by explicit "\n" with a function ?

And the cells in questions are filled up by IMPORTRANGE

So I tried this but no luck:

=REGEXREPLACE(IMPORTRANGE("1LzbgZvRVf1s1nLqz8TkrxAxyJs1CVBuEbOEmmte60Wg","E3:E55"),"(\r\n)",char(10))

2 answers

  • answered 2018-10-11 19:30 Paul

    What you have written swaps a line-break with the whitespace character, char(10), that inserts a line break. So, if your input in a cell contains a line-break (by pressing CTRL and ENTER between some inputs on a single cell, for example) then this your formula is replacing the "invisible" line-break char with another invisible line-break.

    If you remove "\r" and swap char(10) for X, you'll see it works and replaces the whitespace line-break with "X".

    I.e.

    =REGEXREPLACE(A1,"\n","X")
    

    If you want to insert the line-break character, for example, for use in a HTML file, then you could use

     =regexreplace(regexreplace(B2,"\n"," #n "),"\#","\")
    

    Please clarify your question if I have misunderstood.

  • answered 2018-10-12 09:22 Max Makhrov

    =REGEXREPLACE(A1,"\\n|\\r",CHAR(10))

    • | -- replace char A OR char B
    • \\ -- first slash escapes special symbol -- slash.