Is there a limit to use IF(ISNUMBER(Search()))) ?It seems to stop at 21st.

TB 21 Reputation points
2021-01-08T03:36:43.537+00:00

I seem to stumble upon searching for and use IF(ISNUMBER(Search(()) or so 20 times, because I have more than 20 words to search for, but it seems to stop letting add more or it stops working after the 20th? Is that a problem with the IF(ISNUMBER(search ()for?

Or is there a better VBA way to write to look up for a multiple key words? Which is a very long list of words

Any help would be appreciated it.

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,532 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 946 Reputation points MVP
    2021-01-08T10:40:10.157+00:00

    Do you want to know if at least one of the word occurs in a string? If so, you can use the following array formula confirmed with Ctrl+Shift+Enter:

    =ISNUMBER(MATCH(TRUE,ISNUMBER(SEARCH(A1:A30,D1)),0))

    where A1:A30 contains the words you're looking for and D1 is the cell containing the string to search.

  2. TB 21 Reputation points
    2021-01-08T14:54:52+00:00

    Yes HansV-7609. If it's true, I also would like to concatenate that word, word from the array, with another word, say "~Equipment".

    So, the end result would be: "Radio~Equipment". The word "Radio" is from a search of word from the array.

    Is that do-able?

    0 comments
  3. HansV 946 Reputation points MVP
    2021-01-09T08:45:47.643+00:00

    But what if multiple words match? Or is that impossible?

    0 comments