Sorting A-Z then AA-AZ etc
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" wrote:
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?
|