How to extract a particular word(s) from a cell in excel that matches a list of values

Hello everyone,
I have different text strings in each cell of coloumn A and wish to extract only certain word(s) from it that are present in a list of items I have noted down in coloumn B. Here is a brief example of a sample text string and the list of items:
Column A:
"Agricultural activities in Vietnam generate about 62milliontonnes of biomass (rice straw, rice husk, bagasse, corn cob, corn stover, etc.) annually. In this work, four different types of biomass from Vietnam, namely rice straw, rice husk, factory bagasse, and corn cob, have been studied as potential raw materials to produce bio-oil by fast pyrolysis technology. Test runs were conducted in a fluidized-bed reactor at a temperature of 500°C and residence time less than 2s. Size and moisture content of the feed were less than 2mm and 2%, respectively. It was found that yields of bio-oil as a liquid product obtained from pyrolysis of these feedstocks were more than 50% and that obtained from the bagasse was the highest. Bio-oil quality from Vietnamese biomass resources satisfies ASTM D7544-12 standard for pyrolysis liquid biofuels. These results showed the potential of using biomass in Vietnam to produce bio-oil which could be directly used as a combustion fuel or upgraded into transportation fuels and chemicals"

Column B:
List of items:
straw
stover
leaves
shell
peel
cob
piassava
stalk
root
tuber

I have used the following formula that works, but it extracts only the first matching in the text string namely 'straw'. But I wish to also obtain any other names that are present in the list of items and display them comma seperated.
=IFERROR(INDEX($B$1:$B$86,MATCH(1,COUNTIF($A1,"*"&$B$1:$B$86&"*"),0)),"")


Looking forward to a response.
Thanks in Advance,


Ankit

How many items do you have in column B?

Pete

I have just posted a sample of a list of items from one list labelled 'fuel crops'- I actually have 9 such lists each with varying number of items. The list with the largest number of items has 135 entries.
Hope this answers your question?

Ankit

In that case I don't think a formula solution would be suitable - it would be very "clunky". Perhaps you should be asking in the VBA forum, and it would help if you attached a sample Excel workbook.

Pete

Hi Ankit

What version of your Office?
Try this (from Office 2010) in B2 and copy forward

Formula:

Please Login or Register  to view this content.

where A2 have your text and D5:D14 have the list of items.

Try:

Please Login or Register  to view this content.

Use:

=Extract_word(A1,$B$1:$B$86)

@Pete: Thank you for your reply and will keep that in mind for future posts.

@José: Thanks for the formula- it worked and looks to be the most suitable if I want the results in seperate cells and not comma-seperated.

@Phuocam: Thank you for the code- worked really well. Just one issue still remains- it isnt case sensitive and so its not able to pick up the names which start with a capital letter in the text string.
For example it picks up 'straw' but not 'Straw'.
Any idea how I can adapt the code to include this?

Ankit

Change:

If InStr(Str, Cll.Value) > 0 Then

To:

If InStr(LCase(Str), LCase(Cll.Value)) > 0 Then

Perfect!
Thank you very much

You are welcome.