Create New Column With Company Name Which Is Embedded In Email Name

I am working on a project. As part of project, emails were sent out to an experimental group for a cost survey. I have to combine data and do analysis now. I am given two excel spreadsheets (sample of both attached).

In sheet 1, column B contains email name. For instance, first email name (Row A2) is ‘96 '07 Bustello Third email name (Row A4) is 90-pt Panda Express '10

Based on this email name, I need to create a new column that contains only company name i.e Bustello against row A2, Panda Express against Row A4 and so on.

I thought of using ‘Text to Column’ function in the beginning. However, since there is no common character in all email names and no fixed width at which company names are embedded in email name I could not use it.

For now, I am doing it manually. I have 10,000 email names and it is becoming hard to do it manually.

Although I am given another sheet (sheet 2 of sample), which has two columns: Company number and Company Name, there is no common variable that I can use to do VLOOK UP therefore; I am not sure how to make use of that sheet.