I have a spreadsheet that contains 20,000 cells.
I want to select all cells that contain "foobar".
How can I do this?
I 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