Find some words in a string

[larrygoldstein]

Hi all,

Let me explain you the problem I am facing. I have a list of words, let's say for example: "butter", "honey". I would like Excel to extract, from a cell, all the words from the list that are used in this cell. Let's have an example:

I love butter but not honey	butter honey
I don't like butter	butter
I like cherries
I always eat honey, it's the best food ever.	honey

Have you got a solution with a formula or a macro ?

Best regards,

Larry.

[Olly]

A small UDF does the trick:

Please Login or Register  to view this content.

Use as:

Please Login or Register  to view this content.

[larrygoldstein]

Thank you very much that's great ! Now, instead of writing the list of words in the VBA code, I would like to refer to a specific cell (let's say A1) in an other Excel file (let's say example.xlsx in the same folder) that would contain the following value: " "honey", "butter" "

How can I do it ? Thanks

[Olly]

try:

Please Login or Register  to view this content.

Use a cell in this workbook to link to the list of words in another workbook, if necessary, and reference the local range in the function (commented line).

[larrygoldstein]

Great ! Thank you very much for your help

[Richard Buttrey]

Thank you very much that's great ! Now, instead of writing the list of words in the VBA code, .....
How can I do it ? Thanks

Hello Larry,

Just an observation which I hope you'll accept in the way it's intended. When posing a question try to make sure all facts or requirements are known in the first post. This avoids people needing to revisit the task. On this occasion I guess it wasn't too onerous but I've seen threads when the final requirement for a real world workbook is far removed from an example and simplified original workbook.

[larrygoldstein]

Hello Richard,

No problem, you do well to remind people the rules.