Google Sheets – How to Remove a New Line in a Cell

google sheets

I frequently have a problem when entering a row of data that one or more cells will get a spurious newline at the end of their data, and that row will become double-tall. Sometimes I can guess the offending cell, click on it, backspace out the newline, which also removes the last character of the previous row, and then TAB to the next cell. Sometimes this works, sometimes it doesn't. Lately, I've just become resigned to having sheets with inconsistent row height.

+-----+-----+-----+
|     | B   |     |
| A   |     | C   |
+-----+-----+-----+

Example: If I click in Cell B, it is B plus a newline. If I hit backspace, I go up a row and remove the B. If I type B again, and hit TAB, then maybe the cell will become single-height, or maybe the cell will remain double-height.

Best Answer

To remove extraneous whitespace (new lines, tabs, spaces) at the beginning or at the end of the cells, one can use trim command. For example, suppose your current data range is A2:F20. In another part of the sheet, enter 

=arrayformula(trim(A2:F20))

then copy the results and paste values (CtrlShiftV or an equivalent) back to the original range.

If you only want to remove linebreaks (not other types of whitespace) at the beginning or the end, do the same thing but using the formula 

=arrayformula(regexreplace(A2:F20, "^\n|\n$", ""))

And if you want to remove all linebreaks (wherever they appear), use

=arrayformula(regexreplace(A2:F20, "\n", ""))
Related Topic