6

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.

Improve this question
7
  • Can't you just delete the newline? Put your cursor right after the 'B' and press delete?
    – ale
    Commented Aug 11, 2015 at 19:24
  • 1
    For what it's worth, I've not been able to reproduce this.
    – ale
    Commented Aug 11, 2015 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
    Commented Aug 11, 2015 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 - Volunteer Moderator - ♦
    Commented Aug 12, 2015 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
    Commented Aug 12, 2015 at 17:04

2 Answers 2

Reset to default
3

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", ""))
Improve this answer
2
  • 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
    Commented Aug 20, 2015 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
    Commented Aug 20, 2015 at 0:58
0

Simple solution (for data only).

These steps remove hidden code causing the extra line in the cell:

  • Select the cell that is formatting oddly
  • Select and copy the text in the formula bar
  • Click in a different cell, then click back into the oddly formatted cell
  • Paste the contents back into that cell
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.