I have a spreadsheet that contains 20,000 cells.
I want to select all cells that contain "foobar".
How can I do this?
-
What do you mean by "select" and why do you need to select them ? – pjmg Aug 9 '19 at 10:34
-
@PaulJ - Select them as in highlight them so that they can be copied, formatted, etc. – Pikamander2 Aug 9 '19 at 13:19
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
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"
