Alphanumeric sort

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

I would like to sort a worksheet according to the contents
of a cell. The cell contains letters and numbers in
combination - e.g. 166, 1001, TK654, 101, etc. I would
like these to appear in the order 101, 1001, 166, TK654.
I have formatted the cells as text, but I cannot induce it
to sort as I want. Help seems to suggest this is
possible, but no doubt they left out a few conditions,
because the numbers are sorted 101, 166, 1001, TK654.
 
Hi Bruce,
The order that you have given as an example is not sorted as text where a 1
in the third position will
appear after a 0 in the third position and hence 101 and 1001 should be in
reverse order.
If the example that you gave should have been
1001,101,166,TK654
then that can be achieved by formatting the column as Text, entering the
data and then using the sort option.
Alok
 
My mistake on the sort order. The order you gave was the
correct one. Turns out that the difficulty I was having
is a bug in Excel (it is not even recognized as a problem
at the MS "Knowledge" Base, but newsgroup postings make it
clear that it most certainly is a problem). I solved it
by finally locating a posting that had me insert a column
(C in my case), format it as text, then enter =B2&"" into
C2. I copied that down, then Cut > Paste Special > Values
back into column B (also formatted as text). Finally the
sorting worked as expected. Apparently sort works
differently when you format the spreadsheet beforehand
than it does when applying formatting to existing cells.
I really appreciate the prompt reply. Thanks for taking
the time to answer.
 
Back
Top