# Thread: Sorting alphabetically (XP SP2)

1. ## Sorting alphabetically (XP SP2)

Good afternoon

I have just spent several hours re-arranging a list to get it into alphabetical order (I can hear you all shouting now, USE THE SORT BUTTON) because the sort button does not arrange it alphabetically for me. I have a list of 2638 UK postcodes kind of like

B1
B2
B3
B4
Through to B99

When I use the sort button it arranges them out of order, for example

B1
B10
B11 etc. to
B19
B2
B20

I have just manually changed all of them by inserting rows, cutting, pasting and deleting rows and then saved it, however when I have opened it again it has re-arranged them.

My scalp is bleeding from pulling out my hair, any suggestions please (I cannot have them showing B01, B02 etc.)

Thanks

Steve

2. ## Re: Sorting alphabetically (XP SP2)

Steve

This is a brainstorm moment but how about adding another (hidden?) column with this formula:

=IF(LEN(A1)=2,LEFT(A1,1)&0&RIGHT(A1,1),A1)

and copy down, you can copy | paste special | value in the real coulmn and the delete.....just an idea

3. ## Re: Sorting alphabetically (XP SP2)

Sort is sorting the column into the correct order. "1" comes before "2" in the sorting order, and therefore all of the 1's in the second position will come before the 2's etc.

You could insert another column in the data, lets say column B. If your "postcodes" start in A1, then put this formula into B1:

<code>
=LEFT(A1,1)&TEXT(MID(A1,2,99),"00")
</code>

Fill this formula down as far as the "postcodes" go. Now sort both columns on column B.

4. ## Re: Sorting alphabetically (XP SP2)

If you are going to be doing this frequently consider making a custom list and sorting by it. Briefly:

- to create the list, drop a complete list of possible postal codes in an Excel column then use Tools | Options | Custom Lists to save the list.

- to sort it, select Data | Sort, click the Options button and in the dropdown in First Key Sort Order select your custom list as the sort mode.

5. ## Re: Sorting alphabetically (XP SP2)

Thanks all for the responses

I must be losing it somewhere though, in the attached sample workbook column A represents the original figures, column B using Jezza's formula and column C using Legare's, however the sort always seems to come out the same?

It is obviousely me but I can't see why

Cheers

Steve

6. ## Re: Sorting alphabetically (XP SP2)

Thanks John I will try this when I get this column sorted (I just had to do it, did'nt I)

Cheers

Steve

7. ## Re: Sorting alphabetically (XP SP2)

The sorting doesn't work because you provided incorrect information. The entries do not consist of ONE letter followed by a number but of TWO letters followed by a number. You must modify the formulas to take that into account. See attached version

8. ## Re: Sorting alphabetically (XP SP2)

They are sorting the same way because the data structure on your spreadsheet is different than your original post Spreadsheet is 2 alpha characters and then the number, while your sample data is one alpha character and then the number. If the number of alpha characters are going to be different lengths, you'll probably need a UDF.

9. ## Re: Sorting alphabetically (XP SP2)

Thanks and sorry

I made the assumption that people were familiar with UK postcodes!!

2 problems though that you may be able to help me with on this

1. Sorting your example returned AL1 as AL01, AL2 as AL02 etc. which I can't have
2. Some UK postcodes are 1 alpha 2 numeric, some are 2 alpha 1 numeric or 2 alpha 2 numeric (B1,B10,AL1,AL10 etc.)

Is ther any way that this can be worked around.

Thanks as usual

Cheers

Steve

10. ## Re: Sorting alphabetically (XP SP2)

Thanks mbarron

Hans beat you to the punch by 1 minute

Cheers

Steve

11. ## Re: Sorting alphabetically (XP SP2)

1. The extra column is only meant for sorting, not for display. You'd keep the original column for display.

2. You see how important it is to provide complete and correct information. You cannot expect readers to know and understand the arcane UK post code system, probably the least logical system in existence.

You could use this array formula in B1 and fill down:

=MID(A1,1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(\$1:\$10) ,1)),0)-1)&TEXT(1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW( \$1:\$10),1)),0),255),"00")

Again, this is only for sorting!

See attached version.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•