Micrisoft xcell help???? postcode order

foxyuk

hi guys got a list of prospects however postcodes go from ie

ka11 then ka14 then ka3 how do i put them in order without cut, paste and insert etc

thanks in advance

Trinitrotoluene

Seems quite difficult to do

See here: http://www.mrexcel.com/forum/showthread.php?t=25271

DVardysShadow

foxyuk wrote: »

hi guys got a list of prospects however postcodes go from ie

ka11 then ka14 then ka3 how do i put them in order without cut, paste and insert etc

thanks in advance

It is a difficult one. I don't do excel, but as a general computing way of tackling the problem, I would look at it like this.

By default, the sorting will be

ka1
ka10
ka11
ka2
ka3
ka31
ka32
ka4


If you pack the leading space of the number and then sort, you will get

ka01
ka02
ka03
ka04
ka10
ka11
ka31
ka32

Which puts everything in the right order, but makes it look wrong. So I might have a hidden column with a modified post code, where single digit numbers are packed with a leading 0. and I would have a visible column with the proper post code.

And I would devise some code to modify the post code into the sortable form from the visible column to the hidden one.

Trinitrotoluene

Create a new column and put this in the first row: =INT(MID(A1, 3, 10))

Sort on this column.

foxyuk

Trinitrotoluene wrote: »

Create a new column and put this in the first row: =INT(MID(A1, 3, 10))

Sort on this column.

sorry i dont understand what you mean

googler

I think that assumes that your postcode is in A1, A2, etc - that formula refers to A1. If you create new column, put that formula in the first cell, if necessary change A1 to whereever your postcodes start (B1? D1?), and 'Fill Down' the column with the same formula.......

The MID function extracts chars 3 to 10 from your postcode, the INT function makes sure the result of this is a whole number, and I think for postcodes KA11, KA3 and KA 9 it will generate 11, 03 and 09 for you to sort on.

Is this clear?

foxyuk

googler wrote: »

I think that assumes that your postcode is in A1, A2, etc - that formula refers to A1, and whilst I'm not sure about the MID function, if you create new column, if necessary change A1 to whereever your postcodes start, and 'Fill Down' the column with the same formula.......

postcodes are in column j its drivin me crazy having to cut and paste in address order , although i am pc literate doing this is beyond me

googler

OK, let's break it down - which bit of this is unclear to you?

Create a new column and put this in the first row: =INT(MID(A1, 3, 10))

Sort on this column.

Create a new column.
Put this in the first row (of that column).

Are both these statements clear?

=INT(MID(A1, 3, 10)) - This is what you are to put in the first row of that column, but change the A1 to which column your postcodes are in - in your case J1. Is this clear?

Use Edit - Fill Down to fill the rest of the column with the same formula, then highlight the column and use Data - Sort to sort the contents of the column. Is this statement clear?

Stompa

DVardysShadow wrote: »

It is a difficult one. I don't do excel, but as a general computing way of tackling the problem, I would look at it like this.

By default, the sorting will be

ka1
ka10
ka11
ka2
ka3
ka31
ka32
ka4


If you pack the leading space of the number and then sort, you will get

ka01
ka02
ka03
ka04
ka10
ka11
ka31
ka32

Which puts everything in the right order, but makes it look wrong. So I might have a hidden column with a modified post code, where single digit numbers are packed with a leading 0. and I would have a visible column with the proper post code.

And I would devise some code to modify the post code into the sortable form from the visible column to the hidden one.

I'd agree, that's the way to go. Particularly if the postcodes turn out to be 'full' postcodes of the form KA1 2HJ where the second number would also need to be padded out with leading zeros.

Vomityspice

Why do you need to have them in order??

Why not do an Index / Match formula??? (Similar to Vlookup)

What are to trying to do?

john87_2

try this:
put your post code in column A and this formula in column b

=INT(MID(A1,3,2))

then select columns a and b and sort by b first and a