Replace double line break with double double line break in Google Sheets cells

user_14172631 0
Viewed 105

I'm looking to take a bunch of cells of text in Google Sheets and double the line breaks in each.

So something like this:

Hello

My name is Bob

Would become:

Hello


My name is Bob

I tried using the REGEXREPLACE formula, but I can't seem to get it to replace with a double double line break, it seems like the replacement is not taking the "\n" formatting, only the regex search parameter. Find & replace within Google Sheets is similar - you can search using regex, but then there's no way to replace with line breaks.

Appreciate any help you can offer!

google-sheets-formula google-sheets replace regex
1 Answers

You can capture the line break sequence and repeat it with the backreference as many times as you need.

For example, you can match two line breaks and replace with three:

=REGEXREPLACE(A30, "(\r?\n){2}", "$1$1$1")

enter image description here

If you want to double the double line break use

=REGEXREPLACE(A30, "(?:\r?\n){2}", "$0$0")

That is, $0$0 doubles the matched text.

user_38329700
Related