Lookup Ques - finding value within a string to lookup

[dustinjrichards]

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)

 

[Dave Peterson]

On the second sheet, is this two columns?
| Barns, Josh MD | 87956 |

If yes, then maybe this will work--I assumed that A1 held this stuff:
20040707 072734991 BARNS, JOSH MD DEC 06/2 FMT

=INDEX(Sheet2!A1:A100,MATCH(TRUE,ISNUMBER(SEARCH(Sheet2!A1:A100,A1)),0))

These are both array formulas. Hit ctrl-shift-enter instead of enter. If you
do it correctly, excel will wrap curly brackets {} around your formula. (don't
type them yourself.)

Adjust the range to match--but you can't use the whole column.

And drag down.

(Sheet2!a1:A100 held the table.)

If there is no match, you'll see a 0.

 

[Dustin]

Thanks - I'll give it a try.

I ended up going through it manually, but this will be nice to use in
the future if I ever have to model that messy report again.