7

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.

Screenshot

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.

  • I do not believe that this is possible at this time with Google Docs. – user83899 Jan 1 '15 at 19:57
9

Here is what I did.

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

    Screenshot

  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.

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.

3

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.

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.