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!

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

 

[shg]

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