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

[kdev]

An example:

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…

[anon73440385]

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

[kdev]

Hello,

Many thanks, this works like a charm!

Regards

[kdev]

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.

[anon73440385]

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