I have a list of UK postcodes. How can I use these in a Lookup? The first half of the post code can have either 2, 3 or 4 characters, however the first and second part of the postcode are separated by a single space, which may help. For example I want to lookup the characters prior to the space in a table to locate the correct County.

Example:

B1 = Birmingham
BA4 = Somerset
BB10 = Lancashire

My main table displays the full postcode, e.g. BB10 4AW. I want to be able to take all the characters before the space and use these as a lookup in a master table, which only lists the first 2, 3, or 4 characters against the relevant county, so if I take BB10 4AW, I need to be able to take BB10 as my lookup to return Lancashire, but use the same formaula to be able to identify B1 as Birmingham.

I understand the basic lookup concept, but not how to go about using a variable number of characters to use as the lookup.

Many thanks