How can I select all cells that contain specific text in Google Sheets?

Question

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?

Answer 1

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.

Answer 2

If "foobar" is all in one column, I think a simple solution would be to create a filter, then click the dropdown to select foobar.

Check out the section titled "Filter your data" on this link https://support.google.com/docs/answer/3540681

Answer 3

You can automatically select cells that contain the word or phrase you enter in a magic cell by installing the quickFind_ script. Please try this code:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error('Please do not run the script in the script editor window. It runs automatically when you hand edit the spreadsheet.');
  }
  quickFind_(e);
}

/**
* Finds cells that match the regular expression entered in a magic cell.
*
* @param {Object} e The onEdit() event object.
*/
function quickFind_(e) {
  const sheets = /^(Sheet1|Sheet2|Sheet3)$/i; // use /./i to make the function work in all sheets
  const magicCell = 'A1';
  const sheet = e.range.getSheet();
  if (!e.value
    || e.range.getA1Notation() !== magicCell
    || !sheet.getName().match(sheets)) {
    return;
  }
  let searchFor;
  try {
    searchFor = new RegExp(e.value, 'i');
  } catch (error) {
    searchFor = e.value.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
  }
  const magicCellR1C1 = 'R' + e.range.rowStart + 'C' + e.range.columnStart;
  const data = sheet.getDataRange().getDisplayValues();
  const matches = [];
  data
    .forEach((row, rowIndex) => row
      .forEach((value, columnIndex) => {
        if (value.match(searchFor)) {
          const cellR1C1 = 'R' + (rowIndex + 1) + 'C' + (columnIndex + 1);
          if (cellR1C1 !== magicCellR1C1) {
            matches.push(cellR1C1);
          }
        }
      }));
  if (matches.length) {
    sheet.getRangeList(matches).activate();
  }
}

The function uses a cell of your choice, say A1, as the search box. Choose View > Freeze to ensure the search box is always visible. Then enter the search string in the search box and press Tab to activate matching cells in the active sheet. You can also use a regular expression as a search string.

Answer 4

Let's say you want to search for "foobar" in the A1:B10 range.

Use a IF formula with a condition based on =REGEXMATCH :

IF the range contains "foobar", return the cell, else we do nothing. You can return the cell data but also coordonnate with =ROW() and =COLUMN()

You want to test every cells so I made an array with ArrayFormula.

=ArrayFormula(if(REGEXMATCH(A1:B10;"foobar");A1:B10;))

Edit : this method is case-sensitive, space-sensitive, etc... try using =TRIM or =LOWER

Answer 5

Let's say you have interested cell range B1:B10. In order to show all cells which contain case insensitive text "Text2Search", try the following:

=ArrayFormula(If(IfError(Search("Text2Search",B1:B10),0)>0,B1:B10, "NoMatch"))

Note, that "NoMatch" can be replaced with any other text or even just empty "". Hope this help!