how to sort alphanumeric entries

[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

 

[Pete_UK]

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

 

[Billy Liddel]

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

 

[Claus Busch]

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.

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

 

[Trainer]

Thanks for all your suggestions. I'll give them a try.
Trainer.

 

[Bernd P]

Hello,

I suggest a COUNTIF + COUNTIF approach as shown here:
http://sulprobil.com/html/sorting.html

Regards,
Bernd