Remove line break within cell google spreadsheet

Question

Is there an easy way to remove the line breaks within each cell?

Each cell on column E, has extra line that I am having to manually remove, any easy to remove all.

Electrodynamics

Answer 1

How about following sample? This sample supposes that the line break is \r\n and \n.

FLow :

1. Retrieve the information of line break.
2. If the line break is \r\n, when the number of \r\n is more than 2, it is modified to char(10).

Sample :

=IF(REGEXMATCH(E1, "\r\n"),REGEXREPLACE(E1, "(\r\n){2,}", char(10)),REGEXREPLACE(E1, "(\n){2,}", char(10)))

Result :

If you want to remove all of the line break, you can use =CLEAN(A1). In this case, the result of the result sheet becomes sample1sample2sample3sample4sample5.

If this was not helpful for you, I'm sorry.

Louislouisa

CLEAN is the magic here that really removes everything if you have no idea what is the problem. – Intimist

Answer 2

Easiest method:

1. Bring up the Find and Replace box (Ctrl+h on Windows)
2. Tick the box Search with regular expressions
3. In the Find input field, type \n
4. Leave the Replace with field empty
5. Click Replace all
6. (If there are still spaces left, click Replace all again)

Stanley

Good answer!!! For my context, I will use the CSV output to do a data migration. How to tip for other viewers, replace the line break to a distinct token, eg. ###, if you need to do a rollback. – Virgiliovirgin

Answer 3

Here is the formula I use:

=TRIM(SUBSTITUTE(E2,CHAR(10)," "))

The value CHAR(10) will identify new lines and replace it with a " ".

And the TRIM will remove multiple white spaces.

Hesperides

Answer 4

Here is my preferred method to remove leading and trailing line breaks. Note that this will not remove any line breaks in the "middle" of your cells.

• Right click on Column E > Insert 1 Right
• In column F in the first row that has an extra space: Type the formula =TRIM( select the E cell directly to the right, and enter a closing ). For example, in F2 the formula should look like =TRIM(E2).
• This cell should display the correct value. Select this cell and drag the little blue box in the bottom right corner to the bottom of your sheet. This will copy the formula to all other cells, updating the row E references.
• When all the data in row F looks as expected, click on the very top of the row to select it and click copy.
• Right click on the top of row F again and select Paste Special > Paste Values Only.
• Now that Column F has the data you need, you can right click on the top of Row E > Delete column.

Ozonide