4

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.

  • Can't you just delete the newline? Put your cursor right after the 'B' and press delete? – ale Aug 11 '15 at 19:24
  • 1
    For what it's worth, I've not been able to reproduce this. – ale Aug 11 '15 at 19:26
  • 1
    Can you share a spreadsheet where this happens? Also, is your objective to remove all newlines from the cells, wherever they may have occurred, or only some? – user79865 Aug 11 '15 at 19:48
  • 1
    Did you tried with your browse in private / incognito / safe mode? What OS and brower are you using? Take a look to webapps.stackexchange.com/questions/79171/… – Rubén Aug 12 '15 at 1:04
  • Is this vertical inconsistence caused by varying cell alignments rather than newline characters? Try selecting these cells and setting their alignment rule to top-align. – Jerry101 Aug 12 '15 at 17:04
2

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", ""))
  • This is very much the idea, thanks. Is there a way to do this in-place without having to paste the data back in? – dannyman Aug 20 '15 at 0:56
  • Yes, but it requires a script. getValues, process each with JavaScript method replace using a regular expression as above, then setValues. But putting this together will be more trouble than copy-pasting. – user79865 Aug 20 '15 at 0:58

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.