Look for multiple substrings in a sring

**Kman287** · 04-08-2013, 07:50 AM

Hi all,

I have a worksheet that contains a list of keywords as well as a very long list of sentences.

I am trying to build a very efficient methodology that would allow me to flag each sentence if at least one of the substring is found in its string.

I am actually symplifying the question since I have many categorized list of substrings I would actually look for in separate columns.

I have tried many methodologies such as having search functions with OR condition, Inxdex & match etc.... unfortunately all these formula take a very long time to process and I am looking for another solution that would compute much faster than this.

Thank you,
Kamn287

**Mayda89** · 04-08-2013, 07:55 AM

Hi and welcome to the forum,
I think what you are looking for are SEARCH and FIND functions (the choice among these two will depend on your preference regarding to the CAPS)

Hope this helps.
Cheers

**Kman287** · 04-08-2013, 08:07 AM

Thanks Mayda89!

The match doesn't have to be case sensitive. I guess I am struggling more with optimizing the solution than the actual logic. I used a few mothodologies that work they are just taking tooo much time to compute on excel.

Kman287

**Mayda89** · 04-08-2013, 08:22 AM

Hi Kman,
If you don't want it to be case sensitive, you should use the "SEARCH" function.
Does it solve your problem?

Cheers,

**Fotis1991** · 04-08-2013, 08:32 AM

Give a try to this.

Assuming that in B2 starts the list of sentences. In Sheet 2!(i named it "Key Words) in A2:A66 exist the KEY words..

Then in C2 of your first sheet put this and copy down.

=LOOKUP(2^15,SEARCH('Key Words'!$A$2:$A$66,B2),'Key Words'!$A$2:$A$66)

**Kman287** · 04-08-2013, 09:03 AM

Hi Fotis1991,

Well, this is fast compare to what I initially tried.

I will continue to test but it seems that this will perfectly fit my need!

Thanks a lot!