如何在Google表格或Excel中使用多个关键字进行过滤 [英] How to filter using multiple keywords in google sheets or excel

问题描述

我正在使用Google表格，并且我一直在尝试根据B值是否包含多个关键字中的任何一个来过滤数据.我正在尝试对帐户数据进行排序，并且名称不一致，所以我不能只说= FILTER(C:C，(B:B ="BK's Stuff")+(B:B ="Book")).我需要能够从很多文本中提取信息的东西，例如通配符.单个条目最有用的是:

I'm using google sheets, and I've been trying to filter data based on if the B value contains any of multiple keywords. I'm trying to sort account data, and the names aren't consistent, so I can't just say =FILTER(C:C,(B:B="BK's Stuff")+(B:B="Book")). I need something that will take information out of a lot of text like a wild card. What works great for a single entry is:

= FILTER(C:C，SEARCH("BK"，B:B))

=FILTER(C:C,SEARCH("BK",B:B))

但是我不知道如何组合它，因此它将过滤包含"BK"或"Book"的所有值.

But I can't figure out how to combine it so it will filter all values that contain EITHER "BK" or "Book."

谢谢.

推荐答案

您可以通过组合 REGEXMATCH 和 ARRAYFORMULA SEARCH >

REGEXMATCH 允许您搜索由 |

You can do it replacing SEARCH through a combination of REGEXMATCH and ARRAYFORMULA

REGEXMATCH allows you to search for multiple keywords separated by |

= FILTER(C:C，REGEXMATCH(B:B，"BK | book")= TRUE)

注意:

Regexp区分大小写，因此您需要单独指定 REGEXMATCH(B:B，"BK | bk | Bk | bK |")等.

Regexp is case sensitive, so you need to specify separately REGEXMATCH(B:B,"BK|bk|Bk|bK|") etc.

这篇关于如何在Google表格或Excel中使用多个关键字进行过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！