英文:
How can I search through an Excel array to find specific text and then generate a new array that contains the values from those cells?
问题
我有一个大量的有机化合物列表，其中一些含氯，而一些不含氯。我尝试编写一个函数，它会浏览有机化合物列表并生成一个新的仅包含含氯化合物的数组。以下是我目前的结果：
可以看到，该函数正确地提取了所有含氯化合物，但理想情况下，我希望得到一个忽略所有不符合条件的化合物而不是返回“no”值的列表。我在这里漏掉了什么？
英文:
I have a large list of organic compounds, some of which are chlorinated and some are not. I have tried to write a function that looks through the list of organic compounds and generates a new array of only those compounds which are chlorinated. Here is the result I have so far:
You can see that the function is correctly pulling all chlorinated compounds, but ideally I'd like to have a list that ignores all compounds which do not meet the criteria instead of returning a "no" value. What am I missing here?
答案1
得分: 1
以下是您要翻译的内容：
根据OP的要求，将其作为答案发布。
使用 FILTER() 函数可以完成任务，只需忽略 IF() 部分。
也就是说，公式将是：
=FILTER(A3:A83,ISNUMBER(SEARCH("chl",A3:A83)),"")
英文:
As asked by OP, posting it as answer.
Using FILTER() Function does the job, only have to scrap the IF() part.
That said the formula will be:
=FILTER(A3:A83,ISNUMBER(SEARCH("chl",A3:A83)),"")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论