Sorting combinations of letters and numbers
Sorting combinations of letters and numbers
(OP)
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?
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?





RE: Sorting combinations of letters and numbers
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.
RE: Sorting combinations of letters and numbers
RE: Sorting combinations of letters and numbers
My problem is that I need to sort both Numbers and Numbers+letters in a way that is not Excel's default way.
RE: Sorting combinations of letters and numbers
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.
RE: Sorting combinations of letters and numbers
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.