英文:
Problem with multiple SEARCH conditions in Excel
问题
以下是要翻译的内容：
我想从Excel表格中提取不包含特定字符串的单元格值。在这种情况下，有三个子字符串 "COMP"、"CAB" 和 "GRAN"。
不起作用的公式如下：
=IFERROR(IF(OR(SEARCH("COMP",A1),SEARCH("CAB",A1),SEARCH("GRAN",A1)),"",A1),A1)
实际上，我不明白为什么会这样？因为具有单一条件的相同公式运行正常：
=IFERROR(IF(SEARCH("COMP",A1),"",A1),A1)
英文:
I want to extract cell values from Excel table which don't contain certain strings. In this case there are tree substrings "COMP", "CAB" and "GRAN"
The formula which doesn't work is as follows:
=IFERROR(IF(OR(SEARCH("COMP";A1);SEARCH("CAB";A1);SEARCH("GRAN";A1));"";A1);A1)
And actually I can't understand why is that? Because the same formula with a single condition works just fine:
=IFERROR(IF(SEARCH("COMP";A1);"";A1);A1)
答案1
得分: 4
=LET(ζ,B2:B8,FILTER(ζ,ISNA(TEXTAFTER(ζ,{"COMP","CAB","GRAN"}))))
英文:
Alternative:
=LET(ζ,B2:B8,FILTER(ζ,ISNA(TEXTAFTER(ζ,{"COMP","CAB","GRAN"}))))
答案2
得分: 2
尝试使用<kbd>FILTER( )</kbd>和<kbd>MMULT( )</kbd>的方式。
• 在单元格D2中使用的公式
=LET(
a,B2:B8,
s,{"COMP","CAB","GRAN"},
r,SEQUENCE(COLUMNS(s)),
m,MMULT(N(ISNUMBER(SEARCH(s,a))),r)=0,
FILTER(a,m))
英文:
Try something along the lines using <kbd>FILTER( )</kbd> & <kbd>MMULT( )</kbd>
• Formula used in cell D2
=LET(
a,B2:B8,
s,{"COMP","CAB","GRAN"},
r,SEQUENCE(COLUMNS(s)),
m,MMULT(N(ISNUMBER(SEARCH(s,a))),r)=0,
FILTER(a,m))
答案3
得分: 1
另一种FILTER函数的变体：
=FILTER(
A3:A9,
BYROW(
A3:A9,
LAMBDA(arr,
AND(NOT(ISNUMBER(SEARCH({"COMP", "CAB", "GRAN"}, arr))))
)
)
)
英文:
Another variation on the FILTER function:
=FILTER(
A3:A9,
BYROW(
A3:A9,
LAMBDA(arr,
AND(NOT(ISNUMBER(SEARCH({"COMP", "CAB", "GRAN"}, arr))))
)
)
)
答案4
得分: 1
谢谢大家！以下的代码片段对我来说最合适，因为它对我来说最有意义 
=IF(OR( IFERROR(SEARCH("COMP",A1),0)>0, IFERROR( SEARCH("CAB",A1), 0)>0, IFERROR(SEARCH("GRAN",A1),0)>0),"",A1)
by ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ
英文:
Thank you all!
The code snippet below works best for me, just because it makes the most sense to me
=IF(OR( IFERROR(SEARCH("COMP";A1);0)>0; IFERROR( SEARCH("CAB";A1); 0)>0; IFERROR(SEARCH("GRAN";A1);0)>0);"";A1)
by ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论