Excel:在单元格中搜索多个术语并返回找到的值 [英] Excel: Searching for multiple terms in a cell and return the found value

问题描述

我正在尝试扫描一列并在单元格内搜索术语. 一旦找到这些术语中的任何一个，就将找到的一个放在另一个单元格中.

I am trying to scan a column and search for terms inside a cell. Once any of these terms are found, place the found one in another cell.

这些术语在一个命名范围内，我称它为"namedrangeOfApps"，具有超过400行.

The terms are in a named range, I call it "namedrangeOfApps" with over 400 rows.

这就是我现在拥有的，但是我不想返回TRUE，而是想返回找到的实际值.

This is what I have now but instead of returning TRUE I want to return the actual value found.

 =SUMPRODUCT(--ISNUMBER(SEARCH({"namedrangeOfApps"},G2)))>0 

一个单元格的例子可能是:

Example of a cell could be:

"Microsoft.Office.v.21"在这种情况下，如果找到Microsoft和/或Office，则将找到的条目放置在另一个单元格中，而不只是放置TRUE.

"Microsoft.Office.v.21" In this case, if either Microsoft and or Office is found" Place the found entry in another Cell instead of just placing TRUE.

原始解决方案的来源

推荐答案

在单元格 A1 中输入文本，请尝试:

With text in cell A1 try:

=IF(ISNUMBER(SEARCH("Office ",A1)),"Office","") & IF(ISNUMBER(SEARCH("Adobe ",A1)),"Adobe","") & IF(ISNUMBER(SEARCH("google ",A1)),"google","") & IF(ISNUMBER(SEARCH("Microsoft ",A1)),"Microsoft","")

虽然确实有点强力"，但它易于理解，并且如果存在多个关键字，则返回多个关键字.

While it does appear a little "brute force", it is easy to understand and will return more than one keyword if more than one keyword is present.

EDIT#1:

这是一个小的用户定义功能.它的参数是要搜索的单元格和关键字的列表(在示例中，它是 C 列中的名为"mikee"的命名范围):

Here is a small User Defined Function. Its arguments are the cell being searched and a list of keywords (in the sample, it is a Named Range called "mikee" in column C):

Option Explicit
Public Function RetrieveKeys(rin As Range, KeyList As Range) As String
    Dim s As String, r As Range

    s = rin(1).Text
    RetrieveKeys = ""
    For Each r In KeyList
        If InStr(1, s, r.Value) > 0 Then RetrieveKeys = RetrieveKeys & r.Value
    Next r
End Function

它可以处理很大的关键字列表，如果在多个返回之间需要一个分隔符，则很容易更改.

It can handle a very large list of keywords and if a separator is required between multiple returns, it is easy to change.

这篇关于Excel:在单元格中搜索多个术语并返回找到的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！