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?

  • What do you mean by "select" and why do you need to select them ?
    – pjmg
    Aug 9, 2019 at 10:34
  • 1
    @PaulJ - Select them as in highlight them so that they can be copied, formatted, etc. Aug 9, 2019 at 13:19

5 Answers 5


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


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.


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.


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.');

* 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)) {
  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 = [];
    .forEach((row, rowIndex) => row
      .forEach((value, columnIndex) => {
        if (value.match(searchFor)) {
          const cellR1C1 = 'R' + (rowIndex + 1) + 'C' + (columnIndex + 1);
          if (cellR1C1 !== magicCellR1C1) {
  if (matches.length) {

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.

  • This is almost a link only answer which are not allowed. Please add the essentials parts of the referred resource or add it as comment to the question.
    – Rubén
    May 22, 2021 at 20:46
  • @Rubén The link points to a Stack Exchange resource. Copying the code here would seem wasteful. Please advice. May 22, 2021 at 23:03
  • The rational is that the linked resource might not be available later and that cross-posting answer is allowed and encouraged. For details see Your answer is in another castle: when is an answer not an answer? and Is cross-posting an answer to similar questions on different Stacks acceptable
    – Rubén
    May 22, 2021 at 23:34
  • Hmm... top voted answer: "It probably is a good idea to cross reference an answer, and try to tailor your answer to the specific site" — no tailoring required here. Nevertheless, edited the answer to include the code verbatim. May 22, 2021 at 23:56

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.


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


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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.