Unable to extract name or company # and match to a defined table

I am in the middle of converting a database from one system to another and I want to clean up the data as best as I can first. The data I am converting is a support tracking system where the subject is inconsistent. I want to match the relationship from the support case to the company # which is what our current system doesnt do.

Below is column B in my excel doc. I have a table also called firstlook on another tab. I have 21500 rows of data. Below is column B and below that is a snipit of my table. I am looking for a formula(s) where I can search either name or #. I want my new column to just show the company #. Any help is much appreciated. If you have any questions please respond as well.

B

(FLO) [URGENT] Autofair Nissan of Tewksbury 27372FLO
(FLO) 01/22/2013 - Kenny Ross Auto Gorup -A confirmation
(FLO) 03/22/2013 - SIS follow up
(FLO) 08/09/2013 - Milford Nissan - SIS data requisition
(FLO) 18765FLO and 18766FLO trx failing
(FLO) 21604FLO transactions
(FLO) 26106FLO Community Chevrolet error200s

In my firstlook table I searched specifically for these data. Here is what you would be looking at.

Autofair Nissan of Tewksbury 27372FLO First Look
Milford Nissan Corp 28753FLO First Look
Bothun Nissan 18766FLO First Look
Findlay Toyota Scion Henderson 21604FLO First Look.
Community Ford Lincoln 26106FLO First Look

can you please upload a sample book ! to attach -> go to --> advanced tab select paper clip OR manage attachment

Thanks for the title change

none of the numbers in sheet1 column A match any of the numbers in sheet2 column B, even accounting for the text?

I took a chance that you wanted to change the numbers with FLO so that they match in both worksheets. See if this is what you want. This converts the numbers from the first worksheet to match the format in the second worksheet then there is a formula to tell which numbers are in the other worksheet.