Google-sheets – How to select all cells that contain specific text in Google Sheets

conditional formattingformattinggoogle sheets

I have a spreadsheet that contains 20,000 cells.

I want to select all cells that contain "foobar". In other words, I want to highlight them so that they can be copied, formatted, etc.

How can I do this?

Best Answer

enter image description here

This is a solution involving a workaround with a simple script.

First you want to copy the below formula next to every row in your sheet. This will fetch all cell addresses containing "foobar" on this row.

=REGEXREPLACE("'"&join("', '",Arrayformula(if(A2:F2="foobar",address(row(), COLUMN(A2:F2)), "X")))&"'","'X', |'X'","")

Then you want to put all the addresses in one string using the below formula.

=CONCATENATE(G2:G)

The next step is to paste this string into a selection script like this:

function foobar() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRangeList(['$A$3', '$B$6', '$A$9', '$B$9', '$E$9', '$A$13', '$B$13', '$C$13', '$D$13', '$E$13', '$F$13']).activate();
};

Once you go back to the sheet and run the script it will select all cells containing "foobar"

This is the link to the example.

Related Topic