Extract Name of Law Chamber from Full Address

[OkampoVictory]

Hello All

I have a spreadsheet containing name of suppliers (lawyers) and the full address of their law chambers. I am trying to extract only the name of the chambers from the full address. The name will then populate under "Name of Chamber" column.

I am stumped as to how to go about doing this. As these are law chambers, usually the name of the chambers is "33 Coppers Lane" and so forth.

I have pasted a sample of the data below (names and addresses changed for data protection purposes).

Thanks for your help in advance!

Transaction Date	Supplier Name	Full Address	Name of Chamber (manually typed out)
26-05-16	Nathan Hulk	C/O Dog Building London EC5Y 3AB	C/O Dog Building
01-02-17	Charlie Brown	The Chambers of Charlie Brown 188 Flute Street London EC9A 1AR	The Chambers of Charlie Brown
15-04-17	Peter Griffin	19 New Bridge London EC4V 3AU	19 New Bridge
04-06-13	Fred Flintstone	3 Vericose Buildings White Inn London WC2I 3NH	3 Vericose Buildings
05-07-14	Clark Kent	33 Coppers Lane London WC2A 7EK	33 Coppers Lane
31-01-17	Elmer Fudd	18 Pink Panther Court London DX 478 London/St. Pauls EC4A 6EG	18 Pink Panther Court
19-02-16	Shaggy Rogers	Fees Dept Chambers of Shaggy Rogers QC 9 Gong Square
22-03-16	Angelica Pickles	1 Tiara Court Row London EC7Y 9HH
29-04-17	Patrick Star	Patrick Court Chambers 3 Patrick Court London EC6Y 9IO
24-05-13	Boris Badenov	Gemstone Chambers Gemstone House Temple London EC4Y 7NW
14-02-17	Natasha Fatale	18 Pink Panther Court London DX 478 London/St. Pauls EC4A 6EG

<tbody>
</tbody>

 

[shg]

Looking at those, do you see any logic your very fast but very dumb computer could apply to extract that text?