Sorting combinations of letters and numbers

[gouveia]

I have a list I want to sort in the following way:

original
--------
200
102A
300A
305B
100
101



WANT
-------
100
101
102A
200
300A
305B

unfortunately excel likes to sort all numbers first and all letters second. So I get the following undesired result:

EXCEL'S RESULT
--------------
100
101
200
102A
300A
305B

Any Ideas?

 

[SacreBleu]

Remove the letter, if occurs, by using the LEN, RIGHT, and/or LEFT functions (I don't know if you always have 3-digits of numbers, so can't say exactly which function will work best).
See Thread 770-131895, sort the numbers list. Do a second sort of the letters "suffix", and use CONCATENATE to restore the letter "suffix" to the numerical portion.

 

[SacreBleu]

Thread770-131895

 

[gouveia]

Thank you SacreBleu. However, that thread is on the automatic sorting of NUMBERED lists only.

My problem is that I need to sort both Numbers and Numbers+letters in a way that is not Excel's default way.

 

[Mutt]

One way but tedious :
In an adajacent column use =""&A1 for each row.
Range Value the new column
Now sort new column and when asked select option sort numbers and numbers stored as text separately.

 

[gouveia]

Ok found a solution that is not elligant, but works. I found the solution on the follow web site under "Sorting mixed numbers and alpha, numeric prefix (#mixed)"

http://www.mvps.org/dmcritchie/excel/sorting.htm

Only I made a quick formula rather than their CellValue formula.

=IF(B2="",A2,VALUE(LEFT(A2,3)))

Where column 'A' is my values, 'B' is my dummy row for sorting. Then I just do a multiple sort with column B first and A second.