find word(s) within text string that matches a list of keywords - variant

**cricketnz** · 04-05-2012, 08:18 AM

Hi,

I've got a similar question to
[QUOTE=ecc34_11;2540277]

This is my first post.

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 the first match
The list of keywords is about 50
The problem is that these keywords are within strings of words connected by a colon
e.g the keyword is vr200a
but the string where it is to be found in is for instance bker20:fro33:vr200a or fro33:vr200a or as3bb:ttf200:fr033:vr200a
the string can be of variable length with a variable number of colons in it. Generally the word is found at the end of the string though.

I've tried to modify Haseeb's solution but to no avail.

Hope somebody can help me out.

**rvasquez** · 04-05-2012, 10:45 AM

Hello Cricketnz,

I have attached a spreadsheet that contains the formulas I believe will get you the results you want.

Please let me know if you have any questions.

Thanks!

RVASQUEZ

* if this post was helpful please add to my reputation points by clicking the star. Thanks!

**JBeaucaire** · 04-05-2012, 10:45 AM

That shouldn't be too hard. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook so we can see exactly where/what you want to list these matches and we can offer a specific answer.

**cricketnz** · 04-06-2012, 03:42 AM

Hi RVASQUEZ

It's close but I would like to extract in column B any matches of the keyword list but I failed to say that the keywords aren't always the same size. They vary from 3 to 10 characters

Many thanks for your help so far
Cricketnz

**Pete_UK** · 04-06-2012, 05:22 AM

Why don't you attach YOUR workbook, then we can see for ourselves what your data looks like?

Pete

**daddylonglegs** · 04-06-2012, 06:23 AM

Originally Posted by cricketnz

......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 the first match
The list of keywords is about 50

OK, so assume list of keywords in Sheet2 B2:B50 then try this formula in sheet1 B1 copied down

=LOOKUP(2^15,SEARCH(":"&Sheet2!B$2:B$50&":",":"&A1&":"),Sheet2!B$2:B$50)