I need to relaibly sort UK postcodes in a column
e.g. the codes BS1 0IAA, BS23 3EB, BS2 3XX, BS10 5GT

I need to sort codes as BS1.. BS2... BS3... not BS1... BS10... BS2...
--
David Green, UK

Hi David

Create a helper column with the formula
=LEFT(A1,2)&TEXT(MID(A1,3,2),"00")
Copy down for the extent of your data.
Copy the whole helper column, Paste SpecialValues
Sort the whole block of data using the helper column

--
Regards

Roger Govier


"Aristobulus" wrote in message
...
I need to relaibly sort UK postcodes in a column
e.g. the codes BS1 0IAA, BS23 3EB, BS2 3XX, BS10 5GT

I need to sort codes as BS1.. BS2... BS3... not BS1... BS10... BS2...
--
David Green, UK

Use a helper column

So if BS10 say is in A1, in B1 enter

=Left(A1,3)

which will result in "BS1"

Now copy down column B as required



On Sat, 16 Sep 2006 01:32:01 -0700, Aristobulus
wrote:

I need to relaibly sort UK postcodes in a column
e.g. the codes BS1 0IAA, BS23 3EB, BS2 3XX, BS10 5GT

I need to sort codes as BS1.. BS2... BS3... not BS1... BS10... BS2...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Thank you - can you show me how I should amend formulae for e.g. BS23
--
David Green, UK


"Richard Buttrey" wrote:

Use a helper column

So if BS10 say is in A1, in B1 enter

=Left(A1,3)

which will result in "BS1"

Now copy down column B as required



On Sat, 16 Sep 2006 01:32:01 -0700, Aristobulus
wrote:

I need to relaibly sort UK postcodes in a column
e.g. the codes BS1 0IAA, BS23 3EB, BS2 3XX, BS10 5GT

I need to sort codes as BS1.. BS2... BS3... not BS1... BS10... BS2...

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

What are the rules you want to apply. The original example implied you
wanted just the first of the three characters. If you're looking to
find the characters to the left of the space, e.g. return BS23 when
the full code is BS23 3EB then use the formula

=LEFT(A1,FIND(" ",A1)-1)

Rgds


On Sat, 16 Sep 2006 03:35:01 -0700, Aristobulus
wrote:

Thank you - can you show me how I should amend formulae for e.g. BS23

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________