Sorting UK Postcodes

[Guest]

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...

 

[Roger Govier]

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 Special>Values
Sort the whole block of data using the helper column

 

[Richard Buttrey]

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

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
__________________________

 

[Guest]

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

 

[Richard Buttrey]

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

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

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Richard Buttrey]

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

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

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________