formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryregex
In Google Sheets, I am using the formula to create a unique list of words:
=unique(transpose(split(ArrayFormula(concatenate(B2:E"+")),"+")))
But I want to filter out certain strings.
My unique list looks like so:
On the road
Eggs
Bacon
Bread
Hashbrowns
Spreads
Baked Beans
Coffee
Avocado
Pub Roast
Sandwhich meet
Lettuce
Tomato
mustard
mayo
Tom to suss
James to suss
Crisps
Cheese
Crackers
Muslie Bars
I want to filter out strings like Tom to suss, James to suss, On the road, etc.
In Google Spreadsheets, I would do it a bit different.
=COUNTA(SPLIT(A1, " "))
The SPLIT function is only available in Google Spreadsheet and will split the cell's content on every space (" "). The COUNT function will simply count the instances.
The Excel formula gives the same answer, but a bit more laborious:
=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1
See following answer on which formulae are unique to Google Spreadsheet:
https://webapps.stackexchange.com/a/44719/29140
So you can use the like operator with query like this:
=QUERY(Data!A2:F,"SELECT A, B, F WHERE C='' and E like '__%'")
basically _ means any one character, and % means 0 or more characters - so by using 2 instances of _ before using % it requires there be at least more than one characters, while also allowing it the flexibility of using a wildcard.
Best Answer