Grouping postcodes by UK regions

[Natalia132]

I have to group our customers' postcode details by regions (i.e. Greater London, East Anglia etc...)

It's a huge list- and I was just wondering... what is the easiest way to do that...


It's a huge list of customers' orders which will expand as orders arrive every day. I would like them automatically to be appearing on a separate sheet- showing the region information...

any ideas... please, advise


many thanks,
Natalia

 

[Thorin]

The only way I can think of, is to download the existing postcode lists from the Post Office website, they have all the UK postcodes available to download, including a list of postcode regions, have a look maybe something there will help you !

You will need to register with them, but the service is free and easy to use, have a look it may help you with your problem.

http://www.mailsorttechnical.com/downloads_mailsort.cfm

 

[Richard Schollar]

Hi Natalia

You need some form of lookup table so that you can apply a region to the first part of the postcode. The following shows one way to do this:

Book1
	A	B	C	D	E	F
1	Postcode area	Postcode area name
2	AB	Aberdeen
3	AL	St Albans
4	B	Birmingham		Full Postcode	Extracted Post Code Area	Region
5	BA	Bath		BA4 8GY	BA	Bath
6	BB	Blackburn		BN8 4EW	BN	Brighton
7	BD	Bradford
8	BH	Bournemouth
9	BL	Bolton
10	BN	Brighton
11	BR	Bromley
12	BS	Bristol
13	BT	Belfast
14	CA	Carlisle
15	CB	Cambridge
16	CF	Cardiff
Sheet1

Formula in E5 is:

=LEFT(D5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},D5&"0123456789"))-1)

formula in F5 is:

=VLOOKUP(E5,$A$2:$B$16,2,0)

You will need to extend the Vlookup range for as many regions as you have in your table (I downloaded this from Wikipedia, so the fragment I have may not be wholly accurate).

 

[Natalia132]

thanks, guys...

i think I will use royal mail website... and try to do some sort of tables...

but because of the quantity of postcodes... It'll take a lot of time...