One thing to add, if you have a list of all the relevant post code prefixes, you can just do a pattern match for those using Instr in VBA -- very slow, but very effective. It's unlikely that "TN15" or "WC2V" would appear elsewhere in a long string so you could assume it's a post code and just extract the first 8 chars after that, and then look at cleaning the ends of those up afterwards (i.e. delete everything >3 chars after the central space, where you have a central space of course)
Seems I can't attach files, but I do have a list of all the valid prefixes, so if you want it just PM me with your email address