google sheetsgoogle-sheets-query
I am looking for a query formula that will allow me to select all columns (entire column) that contain a specific word within the column.
You can see a test sheet here: https://docs.google.com/spreadsheets/d/1aP7sDTf0xlxQFK51wiswiXcIMKvUNL_3hpi0qhDCvx4/edit#gid=0
For this example, I would like a query that would scan Sheet1 and select all columns that contain the words "Adam". This would select Columns A-D.
Any help would be appreciated.
Thanks.
Since this is more a regular expression logic update, I can contribute:
Try:
'^(mimz|mi[n-z]|m[j-z]|[n-r]|sa[a-m]|san[a-d]).*'
Just keep in mind that the range above needs to differ by the first character. If not, then pull the common characters out. For mimz-mitz:
'^mi(mz|[n-s]|t[a-z]).*'
Not saying there isn't an easier way -- this just helps you update this particular way in the future.
=ARRAYFORMULA(IF(LEN(A3:A),
MMULT(IFERROR(LEN(B3:E)/LEN(B3:E), 0), TRANSPOSE(COLUMN(B2:E2)^0)), ))
Best Answer
In this case I believe the
FILTERfunction would be a much better fit than theQUERY(which becomes quite convoluted).Please try the following formula:
Two significant differences of
FILTERvsQUERYis that using theFILTERfunction you get to "transfer/preserve" both the numbers' original formatting as well as the empty rows.