How to filter column with advanced filter that matches any string that has a given text?

An example:

Table

I use column ColB to store multiple hashtags. How can I filter the table by ColB with a string to match any string that any cell under ColB has, no matter it’s position?

Example: I want to filter ColB by hashtag2 to give me all rows that have that string, no matter it’s position inside the cell content.

Note: I believe that this can be done with regex but idk how…

Hello,

  • Select your range to be filtered
  • Data -> More Filters -> Standard Filter...
  • Expand Options and set option [x] Range contains column labels
  • Select ColB in *Field name`
  • Select Contains in Condition
  • Type hashtag2 into Value
  • Click OK

Note(s)

  • I’m not aware of any method using regular expressions in an Advanced Filter definition. Use a helper column with REGEX() instead.
  • See the following sample file, which uses a helper column Filter-Standard-REGEX.ods
  • The helper column can be used for a Standard Filter or an Advanced Filter (which obviously overcomplicates things; see sheet Filter).
  • If your want to find whole words only use =IF(ISNA(REGEX(B2;".*\b"& C$1 & "\b.*"));0;1) in the helpers column.

Hope that helps.

[Update] - see OPs comment about whole words only formula

Filter-Standard-REGEX-WholeWords.ods

Hello,

Many thanks, this works like a charm!

Regards

If your want to find whole words only use =IF(ISNA(REGEX(B2;".*\b"& C$1 & "\b.*"));0;1) in the helpers column.

That doesn’t work as expected. I tested with “xhashtag2x” and still returned 1.

It doesn’t fail for me. See updated (with alternate formula) file in my answer.