Sorting issue

**floribunda** · 09-01-2009, 12:05 PM

I am trying to sort a column of data in ascending order but I am not getting the results I expect. An example of the data held in the cells is:

4972-1A
4972-10A
4972-2A
4972-1B
4972-3C

When I sort it in ascending order it sorts like:
4972-10A
4972-1A
4972-1B
4972-2A
4972-3C

I want it to sort as:
4972-1A, 1B, ...
4972-2A, 2B, ...
4972-3A, 3B,...
through to
4972-10A, 10B...
and so on...

Any help on how to sort it in the order that I want would be great.

**ncmay** · 09-01-2009, 01:24 PM

Adding a zero would help.

4972-01A
4972-10A
4972-02A

**DonkeyOte** · 09-01-2009, 01:41 PM

That is an excellent route though worth nothing that the number of zeroes to be added shall be determined by the max number that appears post hyphen in your data set
(ie if >=100 and < 1000 then you would need 000 format, >=1000 and < 10000 it would be 0000 etc...)

**masteff** · 09-01-2009, 02:44 PM

If the values always have the same number digits before the hyphen and at least 2 characters after the hyphen, you could use the following formula to in an adjacent column to "fix" your data:
=LEFT(A1,5)&TEXT(LEFT(RIGHT(A1,LEN(A1)-5),LEN(A1)-6),"00000")&RIGHT(A1,1)

**floribunda** · 09-02-2009, 08:26 AM

Thanks for the help and the alternative options. I have gone for the solution suggested by Masteff as this suits how the data is added into the spreadsheet in the first place. Many thanks!