Tuesday, 27 April 2021

Find and replace in a Google Sheet

The following Google Apps Script is designed to search Google Sheet data for a list of terms that need correcting and the cell colour changing on ones that have been modified. So here is a list of the 'incorrect' words to find and what it should be replaced by, as an example:

  • xray change to X-Ray
  • 1st change to First
  • 2nd change to Second

The discrepancies may have occured from the longevity of the data and/or individual users having their own terminology. So we want to take a list of these words and in this example target a specific column of raw data where they may be present.

1 column contains the words to find and the other column the words to replace with.
A list of words to find and their replacement equivalent.

The tool has a 'Welcome' sheet that requires some input so that you can tailor it to your own needs. The downloadable example in this post has sample data so you can try it out and understand how it works.

The code itself largely consists of two 'For loops' where the first iterates through our list of words to find and then the second (which is embedded in the first) iterates through our raw data column.

The tool can be run from the 'Admin' menu in the Google Sheet.

Download

Download the Find and replace in a Google Sheet here (please use 'File' > 'Make a copy' for your own version).

 

No comments:

Post a comment