Filter a column records that contains a list of strings

I have a large data set and im trying to filter out all the records in a column that contain a string in a list of strings. I tried using a filter; [field1] in ("cat", "hat", "bat", "nap"). This works but doesnt actually capture all the records that contains these strings. What am i doing wrong?

For instance, "dog and the cat", is not coming out in the true portion.

Fyi this data is fictitious for privacy reasons.

Reporting

Data Investigation

Accepted answers

Luke_C

Hi @fitch1892

The IN function looks for exact matches. You would need to use the Contains() function which unfortunately only allows one value to be passed, so you'd need something like:

Contains([Field1],'cat') OR Contains([Field1],'hat') OR ......

You could also consider using a find and replace tool to append the matching keyword and filter on that. If you share some sample data it'd be helpful.

All comments

Luke_C

Hi @fitch1892

The IN function looks for exact matches. You would need to use the Contains() function which unfortunately only allows one value to be passed, so you'd need something like:

Contains([Field1],'cat') OR Contains([Field1],'hat') OR ......

You could also consider using a find and replace tool to append the matching keyword and filter on that. If you share some sample data it'd be helpful.

Emmanuel_G

Hi @fitch1892 ,

Do you want something like this ? ( Contains is insensitive case function by default so "Cat" = "cat" )

fitch1892

That works. I went ahead and just used the contains function which worked perfectly.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703