Results 1 to 5 of 5
  1. #1
    3 Star Lounger WildcatRay's Avatar
    Join Date
    Feb 2010
    Location
    Columbus, OH
    Posts
    232
    Thanks
    22
    Thanked 8 Times in 8 Posts

    Searching for Key Terms in a Text String in a Cell

    I want to search for individual words in a cell containing a detailed problem description so I can have a summarized single key word for the problem.

    A formula like this has its limitations:
    Code:
    =IF(ISNUMBER(SEARCH("leak",[@[CUSTOMER CONTENTION]])),"Leak",IF(ISNUMBER(SEARCH("nois",[@[CUSTOMER CONTENTION]])),"Noise",IF(ISNUMBER(SEARCH("rattl",[@[CUSTOMER CONTENTION]])),"Noise",IF(ISNUMBER(SEARCH("pop",[@[CUSTOMER CONTENTION]])),"Noise",IF(ISNUMBER(SEARCH("sound",[@[CUSTOMER CONTENTION]])),"Noise",IF(ISNUMBER(SEARCH("click",[@[CUSTOMER CONTENTION]])),"Noise",IF(ISNUMBER(SEARCH("clunk",[@[CUSTOMER CONTENTION]])),"Noise","Other")))))))
    I thought I could try this:
    Code:
    =IF(ISNUMBER(SEARCH("leak",[@[CUSTOMER CONTENTION]])),"Leak",INDEX(Table4[Contention_Search],MATCH([@[CUSTOMER CONTENTION]],Table4[Result],0)))
    Table4 has many synonyms for “Noise” in the first column and “Noise” in the second.

    With the second formula, however, I still have many results that come up N/A in spite of the description containing at least one the words in the list that I want it to search for.

    Note: I am using Excel 365. Also, I have entered the second formula as both a regular and an array formula with each yielding the same results.

    Is there a solution that I am missing? Thanks.
    Ray
    OS: All computers are Windows 10 AU Pro (1607)
    Antivirus: Kaspersky; Anti-malware: SpywareBlaster, Malwarebytes, SuperAntiSpyware

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,396
    Thanks
    3
    Thanked 237 Times in 216 Posts
    Which column is which in Table4?

    If it helps, this should work without the additional table:

    =IF(ISNUMBER(SEARCH("leak",[@[CUSTOMER CONTENTION]])),"Leak",IF(COUNT(SEARCH({"nois","rattl","pop","s ound","click","clunk"},[@[CUSTOMER CONTENTION]])),"Noise","Other"))
    Last edited by rory; 2018-06-12 at 08:25.
    Regards,
    Rory

  3. #3
    3 Star Lounger WildcatRay's Avatar
    Join Date
    Feb 2010
    Location
    Columbus, OH
    Posts
    232
    Thanks
    22
    Thanked 8 Times in 8 Posts
    Quote Originally Posted by rory View Post
    Which column is which in Table4?
    Table4 Contention Search is the "Noise" synonyms (1st column), and Results is all "Noise" for each synonym (column 2).

    My search formula does not list the table name because the cell is within the same table as the cell I am searching.

    For those who have not figured this out, I am using tables set up within my spreadsheets in the file.

    Capture.PNG
    Last edited by WildcatRay; 2018-06-12 at 08:28. Reason: Attach image of Table4
    Ray
    OS: All computers are Windows 10 AU Pro (1607)
    Antivirus: Kaspersky; Anti-malware: SpywareBlaster, Malwarebytes, SuperAntiSpyware

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,396
    Thanks
    3
    Thanked 237 Times in 216 Posts
    I've just added a formula above that should work without the separate table.

    It looks like you have your columns the wrong way round in the formula you were using, but it wouldn't work for this anyway as I assume you need to search for each of the items in the first column of Table4 in the text in [CUSTOMER CONTENTION], but the formula does the opposite.
    Regards,
    Rory

  5. The Following User Says Thank You to rory For This Useful Post:

    WildcatRay (2018-06-12)

  6. #5
    3 Star Lounger WildcatRay's Avatar
    Join Date
    Feb 2010
    Location
    Columbus, OH
    Posts
    232
    Thanks
    22
    Thanked 8 Times in 8 Posts
    Quote Originally Posted by rory View Post
    Which column is which in Table4?

    If it helps, this should work without the additional table:

    =IF(ISNUMBER(SEARCH("leak",[@[CUSTOMER CONTENTION]])),"Leak",IF(COUNT(SEARCH({"nois","rattl","pop","s ound","click","clunk"},[@[CUSTOMER CONTENTION]])),"Noise","Other"))
    Thank you.

    I added in the rest of the terms from Contention_Search and it appears to work as I expected.

    I will continue reviewing it to see if, in fact, it is working as expected.

    Again, thank you.
    Ray
    OS: All computers are Windows 10 AU Pro (1607)
    Antivirus: Kaspersky; Anti-malware: SpywareBlaster, Malwarebytes, SuperAntiSpyware

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •