0

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.

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

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" In this case, if either Microsoft and or Office is found" Place the found entry in another Cell instead of just placing TRUE.

Source of original solution

Stryker
  • 5,732
  • 1
  • 57
  • 70
  • No only one value is returned in one hit.. Each cell has a long string of names of applications. I have placed the names that I am looking for (i.e ) "Adobe, Microsoft, Google, Office" in a named range and I want to search each row and when I find an occurrence of these terms, place that term in another cell. (instead of TRUE). Example of a cell could be: "Microsoft.Office.v.21" In this case if either Microsoft and or Office is found" Place the found entry in another Cell. – Stryker Apr 20 '18 at 16:21

2 Answers2

2

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","")

enter image description here

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:

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

enter image description here

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

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • The list of keywords is over 400 which is why I have them in a named range. Although this solution works, I would have to create 400 if(isnumber) statements. Instead of =SUMPRODUCT(--ISNUMBER(SEARCH({"Adobe ","Microsoft ","google ","Office "},G2)))>0 I have =SUMPRODUCT(--ISNUMBER(SEARCH({NamedRangeOfApps},G2)))>0 .. Perhaps I was not clear. – Stryker Apr 20 '18 at 16:46
  • @Stryker see my **EDIT#1** – Gary's Student Apr 20 '18 at 16:58
  • @garys-student Still processing. Super slow on 300,000 row with a named range containing 400 rows. so far 30% done.. – Stryker Apr 20 '18 at 17:29
  • Try on small sample first – Gary's Student Apr 20 '18 at 17:34
  • A little slow but it does the job. Will accept and post a question perhaps there is a better way to approach my problem: I have a list of all apps (30,000) installed on all laptops (5000), col A list the machine ID, Col B list the apps. Hence Col A has duplicates. Since each machine has many apps. And need to find out which apps are win 10 compatible? Any suggestion is welcomed. – Stryker Apr 20 '18 at 18:37
0

try this

=IFERROR(INDEX({NamedRange1},MATCH(TRUE,ISNUMBER(SEARCH({NamedRange1},A10)),0)),"")

or 

=IFERROR(INDEX({"microsoft", "google", "apple"},MATCH(TRUE,ISNUMBER(SEARCH({"microsoft", "google", "apple"},A10)),0)),"")

This works great

reference...

Searching for multiple text strings in a MS Excel cell and return the same string when found

MFarooqi
  • 1,004
  • 5
  • 12
  • 26