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
