Ask Your Question
0

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

asked 2020-08-22 16:57:53 +0100

kdev gravatar image

updated 2020-08-22 16:58:38 +0100

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...

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
2

answered 2020-08-22 18:03:47 +0100

Opaque gravatar image

updated 2020-08-24 17:36:37 +0100

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

image description

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 C:\fakepath\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

C:\fakepathFilter-Standard-REGEX-WholeWords.ods

edit flag offensive delete link more

Comments

Hello,

Many thanks, this works like a charm!

Regards

kdev gravatar imagekdev ( 2020-08-23 21:16:14 +0100 )edit

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.

kdev gravatar imagekdev ( 2020-08-24 17:29:40 +0100 )edit

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

Opaque gravatar imageOpaque ( 2020-08-24 17:33:44 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-08-22 16:57:53 +0100

Seen: 291 times

Last updated: Aug 24 '20