6

I have a query formula that looks into a table and pulls names for example:

   A                   B
1  john, andrew     blah
2  drew, mike       blah

I want it to show me all of the rows that involve drew, the query formula is select where A contains 'drew'. Both rows would show up, because andrew contains drew, but if I use the formula select where A = 'drew', nothing would show up because it is looking for an exact match. What do I do here? I was thinking maybe regular expression would help, but how would I do it?

7
+100

To handle word boundaries, you need where A matches instead of where A contains. It allows the use of regular expressions such as .*\bdrew\b.*, meaning

  • containing the string "drew"
  • with word boundaries \b left and right of the string (i.e., the characters adjacent to it cannot be word characters)
  • with any other characters elsewhere in the string: .*

The last bullet item is needed because the logic of matches requires the entire content of A to match the given expression (unlike regexmatch function, which will attempt to match substrings).

So, in your case it would be

=query(A:B, "select * where A matches '.*\bdrew\b.*'")

or, if you want to also match "Drew" (case insensitive), then

=query(A:B, "select * where lower(A) matches '.*\bdrew\b.*'")
  • is there a way to use regex in countifs? how would the formula change if i use countifs. I've tried countifs(Out!$E$3:$E,".*\b"&B65&"\b.*") – jason Oct 22 '15 at 2:55
  • Not really. countif and countifs support only the basic wildcards ? and *, which is not enough to handle word boundaries. – user79865 Oct 22 '15 at 3:00
  • Also, it looks like you are changing the question from "show me the rows" to "count the rows". Counting can be done with select count(A) or with counta(filter(...)), but this should be a separate question. – user79865 Oct 22 '15 at 3:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.