Find word(s) within text string that matches a list of keywords - NOT case sensitive

Hello,

Previously a poster requested a solution to finding text within a string, given a list of keywords.

Here is a link to the original thread: http://www.excelforum.com/excel-gene...-keywords.html

Relevant text:
Basically, I have a column of text strings in worksheet one which I need to check for the presence of keywords which is listed in another column in worksheet two (the keyword list).

So if any word from the keyword list is found in, say, cell A1 of sheet one, the cell to its right (B1) should have a formula to display either the first match (or if possible, all the matched words).

Factors to be taken into consideration are:
- the column of text in sheet one may be run for up to 500 rows
- the keyword list may contain up to 3000 keywords (3000 rows)
- the match must to be case sensitive
- the match must be for whole words (and not match "office" to "off")
- there are punctuations in the text in sheet one.

Original Solution: find word(s) within text string that matches a list of keywords.xls

This works perfectly.

However, it is case sensitive. Two questions:

1. Is it possible to modify the formula to allow a match to be made if the data in A1 does NOT match the case (upper/lower/mixed) of the keywords in the list?
2. Can the keyword(s) extracted from A1 be in the same case as the Keyword list (not the data in A1)?

I have spent the better part of a day searching for the answer to this and trying many different approaches. My knowledge of Excel is just enough to know that there is a lot I don't know!

Thanks,

gbm

Hi,

Change every occurrence of

Formula:

Please Login or Register  to view this content.

to

Formula:

Please Login or Register  to view this content.

in the formula in column B (count) and the array formula in column C and to the left.

You will ALSO need to change every occurence of

Formula:

Please Login or Register  to view this content.

to

Formula:

Please Login or Register  to view this content.

Hope this helps

Thank you.
Thank you.

This worked perfectly.

gbm

No worries!

Please don't forget to mark this thread as solved