I have a spreadsheet which uses concatenated data as the basis for sumif
calculations looking at another spreadsheet. In separate columns however, I
want to be able to split out the concatenated data.

Column AM has my concatenated data as follows:

Glasgow Property Freehold
South Yorkshire Property Shorthold Lease
Edinburgh Other Telephone System

I want to be able to search in column AM, find the Region (Glasgow,
Edinburgh....) and return that region into column B of the same row. I have
approx 20 regions that I am looking for which are listed in a range called
"Opcos"

I think it's vbTextCompare I need to use but don't know where to start with
writing the code to do this. Could someone please help me with the code