I need to be able to sort a column by letters going from A-Z then AA-ZZ. The problem is, I can only get Excel to sort alphabetically so AA comes before B and C or sorting gives me results like A B CC D EE G etc.
Is there a way I can sort a column as single letters first then double letters (AA, BB, etc) alphabetically?
Any help would be greatly appreciated.
if it's only a single letter or double letter in that column, then perhaps you can use a helper column...
So if those letters are in Column A, starting at A2 then try this formula in another column:
=COLUMN(INDIRECT(A2&1)) copied down
Then sort by the helper column....
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Hello Vittorio
Your formula may fail unless the OP is using xl2007. Another possible helper formula could be:
=SUMPRODUCT(CODE(MID(IF(LEN(A2)<2," "&A2,A2),{1,2},1))*{100,1})
and copied down. Then Sort by this helper column.
Richard
Last edited by Richard Schollar; 09-09-2008 at 11:24 AM.
Hi Richard,
How would it fail? I tested it in 2003 and it seems to work.
No ZZ1 cell in xl2003
Good Point... didn't think of the column number limit....
Thanks Richard.
That worked perfectly! Thanks!!!Originally Posted by NBVC
A little more simplistic.... but this should do too.
=IF(LEN(A2)=1,CODE(A2),CODE(LEFT(A2,1))+CODE(RIGHT(A2,1)))
Sorry to be a pain Vittorio, but again you would need to adjust your formula in a very minor way:
=IF(LEN(A2)=1,CODE(A2),CODE(LEFT(A2,1))*100 +CODE(RIGHT(A2,1)))
this stops YY appearing above ZA
Ok, I better end this one before I embarrass myself (if I haven't already)... thanks for pointing those out to me Richard. Good Eye.
Why not just =RIGHT(" " & A2, 2)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks