Iterating Through a List and Identifying Defined Terms

[JamesPenner]

Hi All,

I originally posted this question in the Formulas and Functions section, but I was advised to move it here. This is slightly modified from the original request. I hope that’s ok.

I want a workbook that consists of three main elements:

1. A column where strings of text can be entered (say A:A)
2. A list of defined terms
3. A column adjacent to the first (say B:B) that returns any of the defined terms in the list that appears in A:A.

For each cell in A:A, I'd like to have Excel go through each term in the list of defined terms, and if any are present, list them in the adjacent cell in B:B, separating the results with commas. I’ve attached a sample that will hopefully clarify what I’m after.

Sample.xlsx

My goal with this is to try to extract keywords that might appear in a long list of directory and file names.
I have an approach now that almost gets me there. It's a formula that will return a term from a list. I then use the Substitute function to remove that term from the original string and search again. Aside from being cumbersome (requiring a new Substitute function for each term I want to check for), it also has a real problem. If I have the terms "winter" and "winter tires" in the list, for example, it'll find one, but not the other.

Any help on how to approach this problem would be greatly appreciated.

Thank you for your time.

- James

[Tinbendr]

Wow! Such a small sampling.

What will you do if two defined terms are in the text?

This code adds it to the next column.

Please Login or Register  to view this content.

[JamesPenner]

Hi Tinbendr,

Thank you very much for your help. I think your code will be enough for me to work with to get the workbook working the way I want.

When you mention "What will you do if two defined terms are in the text?", do you mean how will I deal with repeating terms? If so, I have a UDF I've been using that removes duplicate terms from a cell. I was planning on collecting each of the terms your code creates into a single cell, separating them with commas, and then removing the duplicates. A little convoluted, but still better than where I was with my previous approach.

Thank you again for your help.

- James