I have a spreadsheet that contains 20,000 cells.
I want to select all cells that contain "foobar".
How can I do this?
Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this communityI have a spreadsheet that contains 20,000 cells.
I want to select all cells that contain "foobar".
How can I do this?
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"