Tips or tricks required to sort UK postcodes

I'm not aware of any way to sort other than your idea of creating a separate column in which to extract out the sort characters.

matt..

say your postcode is in a1 etc

use the =left formula..

eg =left(a1,2,etc will give you the postcode

will display the postcode..then you can cancatenate..not entirely sure what you are trying to do but =left will def help

regards
chef

Hi Matt

Can't help with VB but just had a thought. You could create a custom list with your raw data and then use the sort function to sort ascending etc.

As I said, just a thought you would need to experiment.

[Edit]

Tried this out after post. Excel does values, text, logical values, error values and then blanks. I tried creating a custom list in line with how Excel sorts but it got complicated.
This message was edited by Jak on 2002-10-22 16:38

You could sort on the column C value shown below...

Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".
This message was edited by Mark W. on 2002-10-22 17:41

Thanks for your help guys, will be trying the suggestions right away.

I'm using the following code as aided by Mark
to manipulate uk postcodes and then to sort alpha and then numerically. The code works fine on smaller sheets (less than 100 records) and when I step through the code but starts to struggle after 150 records and locks up. Does anyone have any suggestions to make this more efficient?

Thanks

Matt

Sub formulatest()

Dim myrange As Range
Dim lastrow As Integer

Application.ScreenUpdating = False

Columns("K:M").Select
Selection.Insert Shift:=xlToRight


lastrow = Range("A65536").End(xlUp).Row

Set myrange = Range("K2:K" & lastrow)


For Each cell In myrange

cell.Formula = "=TRIM(LEFT(" & cell.Offset(0, -1).Address & "" & ",4))"

cell.Offset(0, 1).FormulaArray = "=MAX(ISERROR(MID(" & cell.Address & ",ROW(INDIRECT(""1:""&LEN(" & cell.Address & "))),1)+0)*ROW(INDIRECT(""1:""&LEN(" & cell.Address & "))))"

cell.Offset(0, 2).FormulaArray = "=LEFT(" & cell.Address & "," & cell.Offset(0, 1).Address & ")&TEXT(RIGHT(" & cell.Address & ",LEN(" & cell.Address & ")-" & cell.Offset(0, 1).Address & "),REPT(0,MAX(LEN($K$2:$K$" & lastrow & "))-" & cell.Offset(0, 1).Address & "))"

Next

Cells.Select
Range("J1").Activate
Selection.Sort Key1:=Range("M2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("K:M").Select
Selection.Delete Shift:=xlToLeft

Application.ScreenUpdating = True

End Sub
This message was edited by Matt on 2002-10-23 10:46