Grouping UK postcodes into the countries' regions

Greetings all,

I'm currently working on a task where I have thousands of UK postcodes and I'd like to specify a region for each (South East, North West, Wales, etc.). I have so far assumed this can be done as the letters in the first section of each postcode give away the area within the country.

As an example, I've got a postcodes list (left column below) and have been able to extract the letters from each using the following: "=LEFT(D3,2---(ISNUMBER(VALUE(MID(D3,2,1)))))". This works perfectly and gives me the column below to the right:

GL12 6PQ............................GL
LE4 2DQ..............................LE
WA6 1LY.............................WA
BB2 2FP...............................BB
NN12 9QS............................NN
B74 0LB...............................B
IP23 3MY.............................IP
B2 3BD................................B
SR1 9VE..............................SR
B70 1DT..............................B

However, I would now like to associate these starting letters with the different regions in Great Britain. As follows:

Postcodes starting with (UB, TW, KT, WD, HA, CR, SM, SW, BR, SE, DA, RM, IG, EN, N, NW, W, WC, EC, E).......London
Postcodes starting with (CT, TN, BN, ME, RH, GU, PO, SO, RG, SL, HP, OX, MK, LU, AL, SG, CM, SS)..................South East of England
(GL, SN, BS, BA, SP, BH, DT, TA, EX, PL, TQ, TR)........................................................South West of England
(CO, CB, IP, NR, PE, LE, DE, NG, LN, S, DN)................................................................East Midlands
(NN, CV, B, WR, HR, WS, DY, WV, TF, ST)..................................................................Midlands

etc. etc. I'm happy to create a new column in my spreadsheet, a new worksheet for this, or do it all in a single column (letter extraction & sorting of area in a single column if possible). Any help will be much appreciated as this table could make my life a lot easier. Many thanks in advance,

J.

Create table like:

	A	B
1	code	location
2	UB	London
3	TW	London
4	KT	London
5	GL	South West of England
6	SN	South West of England
7	BS	South West of England

then use:

Formula:

Please Login or Register  to view this content.

where C1 is results ofyour double letter starting code.