how to sort alphanumeric entries

  • Thread starter Thread starter Trainer
  • Start date Start date
T

Trainer

Using Excel 2007 on Windows XP. We have a column of an assortment of alpha
and numberic entries that won't sort properly. There must be a way to do
this. Thanks for helping.
For example:

1
2
3
28
29
106
106a
51a
51b
 
Could you give some more examples of your range of values? If there is
an alpha, will it only be a single character? If so, what is the range
of letters that you might come across? Will you have a maximum of
three numbers before the alpha?

Pete
 
Assuming that the text is at the end of the string use a helper column in the
next column, and sort on that. if the list starts at E1, in F1 enter
=IF(ISNUMBER(RIGHT(E1,1)+0),E1,LEFT(E1,LEN(E1)-1)+0)

and copy down and sort on column F.

HTH
Peter Atherton
 
Hi Trainer,

Am Mon, 31 Aug 2009 15:58:15 -0500 schrieb Trainer:
Using Excel 2007 on Windows XP. We have a column of an assortment of alpha
and numberic entries that won't sort properly. There must be a way to do
this. Thanks for helping.
Click to expand...

your entries in column A and max. one single character:
In a helper column:
=IF(CODE(RIGHT(A1,1)) > 64,LEFT(A1,LEN(A1)-1)*1000+CODE(RIGHT(LOWER(A1),1)),A1*1000)
and sort by the helper column.


Mit freundlichen Grüssen
Claus Busch
 
Back
Top