CODEWITHSUNDEEP
excelstringsearchsubstring
Santiago Romero Brufau
Santiago Romero Brufau

Reputation: 135

Check whether a cell contains a substring from a list Excel

I'm trying to have Excel check whether a cell contains any of the substrings in a list that I have.

I have a list of medication orders, and I want to find which of those are for antibiotics. I have a list of antibiotics, so I want to check each cell to see whether it has the substring of the antibiotic names on my list.

I have found related responses, and I have tried this:

=ISNUMBER(SEARCH($H$2:$H$3,A5))

Where A5 is the cell I want to check, and H2:H3 contains the list of substrings I am looking for. But this doesn't work. Apparently SEARCH needs a single substring.

I could do it with OR, but my list of substrings is going to have more than 100 names, and it will become unwieldy very fast.

Is there any way I can do this? Thanks

Upvotes: 2

Views: 4364

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

This will return TRUE/FALSE:

=SUMPRODUCT(--ISNUMBER(SEARCH($H$2:$H$3,A5)))>0

Upvotes: 2

Related Questions