19

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

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

Community
  • 1
  • 1
Agent_Sully
  • 255
  • 1
  • 2
  • 10

4 Answers4

25

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)

enter image description here

florian
  • 461
  • 1
  • 5
  • 11
  • 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. – Rafael Gomes Francisco Sep 24 '21 at 14:20
14

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 :

enter image description here

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.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
2

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.
Kyle Polansky
  • 431
  • 3
  • 8
1

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.

Abhishek R
  • 4,087
  • 1
  • 17
  • 21