Finding a word within a sentence

[Michael D]

The title of this post is a little misleading but it's close. I have a spreadsheet which has from 6 to 12 "words" in a column of cells. There are about 2500 rows in all.

each of these "words" could be any number of letters or group of numbers. A space is used as the delimiter. There really isn't a consistent pattern to where each word might be within the cell.

What I'd like to do is have excel look at each "word" in the cell and compare that word to a vlook up table that I'll also create. If I find a match in A2, I'll put it into B2. There would only need to be about 6 entities in the look up table.

I'd repeat this process in 3 columns, as I'm looking for 3 different types of data. Luckily, the creator of the sheet maintained the same naming conventions, it's just that the names are liable to show up at any point within the cell.

Any help would be greatly appreciated!

[mrice]

Suggest posting a copy of the workbook with example output and look up table to help clarify. It doesn't need to have all the rows but should have the same overall structure.

[Michael D]

No need to upload the whole workbook but here's a typical entry:

HANDLE ARTISAN ZN ANTIQUE BRASS M4 CTC 3"

I'd be looking for "ZN", "HANDLE" and "BRASS" in this case.

Here's another:

PULL BR.NI.MATT M4 DIA.30X31MM

In this case I'd be looking for "PULL" and "BR.NI.MATT"

The thing to remember is that the substring I'm seeking can be anywhere within the cell and the cell can have any number of substrings