How do I eliminate extra line breaks in a Google Spreadsheets cell?

Question

I have a spreadsheet in which some cells have multiple carriage returns. I would like to eliminate all the extras, so that four carriage returns shrinks down to just one.

This question is similar to How to freeze row height?, and In a Google Spreadsheet, how can I force a row to be a certain height?, but since there are new lines / carriage returns in the text, turning off text wrapping does not work.

Is there an easy formula to do this?

I can't do it with a simple find/replace because the length of the line breaks varies.

Answer 1

Here is what I did.

1. Hit ctrl+f to search, and click the "..." button representing more options

   

2. Typed \s+$ or \n in the find field

3. I left the replace field empty
4. Clicked "Search Using RegEx"
5. Clicked replace

I hope it works for you. Also, you can replace \n with any regex expression and it can be immensely helpful.

Answer 2

the formula you would want is

=regexreplace("cell with your data","(\n)(\n{1,})","$1")

or

=regexreplace("cell with your data","(\r)(\r{1,})","$1")

the first one replaces new lines and the second on replaces carriage returns if needed.

Answer 3

Well, one way would be to Find:

&char(10) &char(10) &char(10) &char(10)

and Replace with

&char(10)

with Also search within formulae ticked.