Google-docs – How to use text from capturing groups in Google Docs regex replace

google docsregex

I'm trying to match certain text and then replace with the text plus some extra characters. Minimal example text:

#10 Oranges. These are citrus fruits

Desired output:

#10 Oranges.
These are citrus fruits

Regex: (#\d{1,2}[^.]*\.)\s*

Replace with: $1\n

(I have Match using regular expressions checked)

The regex successfully matches #10 Oranges.. However the numbered backreference doesn't replace the text with the capture group but just goes in literally (literal output is $1\n). I have also tried using a backslash for the numbered backreference \1 and the result is the same.

Is there something I'm missing? How do I reinsert matched text in google docs using regular expressions?

Best Answer

Little hacky, but it doesn't require an extra add on script and will likely cover 99% of your use cases. You can still use capture groups with RegexReplace and reference in the replace text with $1 or $2. Just split up your regex into two capture groups and concatenate with a random (infrequently used) character like ~. Then you can take the entire returned value from that and replace ~ with a newline:

=SUBSTITUTE(REGEXREPLACE(A1,"(#\d{1,2}[^.]*\.)(\s*)","$1~$2"),"~",CHAR(10))

screenshot

For more info on the regex syntax used in google sheets, see the re2 spec

Related Topic