Search code examples
regexgoogle-sheetsfind-replace

Find and replace in Google Spreadsheet using Regular Expressions

I would like to use the Google Spreadsheet Find and Replace function with the "Search using regular expressions" function activated to do a search and replace in my document.

Use case: Some of my cells contain erroneous linefeed characters at the end (leftovers from paste operation by some of the editors).

I'm using the following pattern to successfully find the cells

.*\012$

Is there some syntax for the "Replace with" field that lets me replace the cell's content by the string I found minus the \012 character at the end?

The Google Spreadsheet documentation does not contain any relevant information. https://support.google.com/docs/answer/62754?hl=en

Here's a screenshot of the box

Solution

  • You may use a capturing group (...) in the pattern around the part you want to keep, and use a $1 backreference in the replacement:

    (.*)\012$

    to replace with $1.

    See the regex demo.