Puff The Magic Wagon!

iTrader: (2)

I have 50,000+ postcodes in an excel spreadsheet. They are sorted alphabetically.

What is the easiest way to group by the first part of the postcode?

ie SW16 2AA is OK & E2 5AA is likewise grouped? Using the space as the decider in the grouping.

OR

Group by the first 2 characters in the postcode, so by SW & E2 & ME etc etc.

Probably the 2nd is easier but I don't know how to do it...

I'm trying to find out how many addresses per postal zone

Martingb

In the next column, use the LEFT function to take the first two letters. Then a quick pivot table on this new column will summarise the data.

e.g. =LEFT(a2,2)

Martin

dsmith

Puff,

To Cope with the variable length First part try,,

=LEFT(A1,FIND(" ",A1,1)-1) to give the left hand part and
=RIGHT(A1,LEN(A1)-FIND(" ",A1,1)) to give the right hand part.

Then Sort,pivot etc with the new columns.

Deano