Sorting A-Z then AA-AZ etc

[Guest]

Possibly the wrong place for this question, but I am after the best way to
sort a set of data by reference to a column that has 1 or 2 alphabetic
entries (ie A-Z, AA-AZ, BA-BZ, etc).

On its own excel sorts as A, AA,AB - AZ, B, BA etc.

Any suggestions gratefully received. Is this just a job for bubble sorting?

 

[Guest]

sort a set of data by reference to a column
no sure what you mean here. However:

it seems like you want to sort by:
1-length (1 or 2: the length of the string A,B,...AA,AB...)
2-then by alphabetical order

Assuming your data (A,B,...AA,AB...)is in column A starting row 2.
Currently, Excel would sort as:
A
AA
AB
....
B
BA
....

So, in another column, row 2, enter the formula:
=len(A2)&A2
len() returns the length (1 if A,B,... and 2 if AA, AB,...)
so now, instead of A,B,...AA,AB,...
you have: 1A,1B,...,2AA,2AB

That is, if you sort by this new column, excel should first sort the number
(1 or 2) then by the letter (A,B,...):
1A
1B
....
2AA
2AB
....

Would that work for you?
Regards,
Sebastien

 

[Alan Webb]

Sebastien

Thanks, that'll do it. Obvious when you think about it, and annoying
that I couldn't come up with it myself - having done the hard bit
sorting out the code to pick up all the details to create the list
from other files !

Thanks again