谷歌工作表过滤器/正则表达式函数，以任何顺序匹配包含两个或多个字符串的字符串

Question

我在另一张名为 data 的工作表的 A 列中列出了一个数据集。在另一张纸上，当用户键入单元格 B1 时，我使用以下公式来填充单元格。我只想填充过滤后的数据。到目前为止，如果我以正确的顺序搜索文本，这效果很好，但如果顺序颠倒，则不会。任何人都可以帮助将^(?=.*bCreamb)(?=.*bBetadermb).*$纳入公式以使其发挥作用吗？

=ArrayFormula(filter(data!A2:A,(regexmatch(lower(data!A2:A),lower(substitute(B1," ", ".*"))))))

数据列表很长的一部分是：

Camphor USP Crystal <= 2% + Menthol <= 2% in Betaderm (Betamethasone Valerate) Cream 0.1%
Camphor USP Crystal <= 2% + Menthol <= 2% in Betaderm (Betamethasone Valerate) Ointment 
Clindamycin HCl USP Powder <= 5% in Betaderm Cream 0.1% or 0.05%
Clindamycin HCl USP Powder <= 5% in Glaxal Base Cream

Answer 1

您可以改用QUERY：

=ARRAYFORMULA(QUERY(data!A:A,"where lower(A) contains '"&JOIN("' and lower(A) contains '",SPLIT(LOWER(B1)," "))&"'",1))

• where筛选出行
• 比较字符串contains
• SPLIT和JOIN根据B1中的单词数创建动态where Col1 contains {string}字符串

Answer 2

Google 表格使用 RE2 正则表达式，根据设计不支持反向引用和环顾断言。

若要获取单元格中两个空格分隔的单词B1以任意顺序出现的行，请使用以下模式：

=iferror( 
filter( 
data!A2:A, 
iferror( search( regexextract(B1, "^(w+)"), data!A2:A ) ), 
iferror( search( regexextract(B1, "(w+)$"), data!A2:A ) ) 
), 
"(no row matches '" & trim(B1) & "')" 
)

在仅包含一个单词的事件单元格B1中，公式将获得与该单词匹配的行。

如果您需要匹配两个以上的单词，您将在filter()中添加更多条件。

请注意，w将仅匹配[a-z0-9]。如果您需要匹配模式，例如<=或2%，您必须修改regexextract()。

如果需要更复杂的匹配，可以使用regexmatch()代替search()，如下所示：

=iferror( 
filter( 
data!A2:A, 
iferror( regexmatch( data!A2:A, "(?i)" & regexextract( trim(B1), "^(w+)" ) ), true ),  
iferror( regexmatch( data!A2:A, "(?i)" & regexextract( trim(B1), "(w+)$" ) ), true ) 
), 
"(no row matches '" & trim(B1) & "')" 
)