Well this is my first time posting a question - as unfortunately, this
is the first time I've been this stuck on a formula and perhaps am
asking too much from Excel. Any ideas/suggestion are greatly
appreciated!

A column in my spreadsheet (text) contains the strings I want in
another column, surrounded within the same cell) by other stuff I don't
want. The issue is, the specific strings I want to pull out of the
cells vary in length and placement - as far as I know, since there
isn't a standard I can't do a text-to-columns or FIND in this instance
(please prove me wrong!). I have sorted by that column and have done
"text-to-columns" on adjacent selections that do happen to match, but
when dealing with 30-some thousand rows..... I'm just hoping there's a
better way to deal with data this jumbled.

So - what I'd like to be able to do is an advanced lookup function,
where it looks within the string, and if it finds a specifc bit o' text
that matches a string-value I've defined in a separate sheet, it
returns a specific value. For example:

On the original data modeled from a flat file report looks like this:
"20040707 072734991 BARNS, JOSH MD DEC 06/2 FMT"

In another tab, I have built a table that contains:
| Barns, Josh MD | 87956 |

I want Excel to notice that the "Barns, Josh MD" from the first sheet
is in the table on the other sheet.

Unfortunately, each entry in the first sheet was manually keyed into
the system at some point, so there is no standard as to the placement
of the names or where/what data is surrounding it within the cell.

Any ideas? This is driving me crazy!

(names/data have been changed)