If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sort list
I have a table which I need to sort, e.g.:
2000 2039B 2026 2167 2239 2242 When I run Sort, I get 2000 2026 2167 2239 2242 2039B Why does 2039B come at the bottom rather than between 2026 and 2167? |
#2
|
|||
|
|||
Sort list
"janey" wrote in message
... I have a table which I need to sort, e.g.: 2000 2039B 2026 2167 2239 2242 When I run Sort, I get 2000 2026 2167 2239 2242 2039B Why does 2039B come at the bottom rather than between 2026 and 2167? Because numbers are numbers and are sorted numerical. Other data is alpha numeric which are sorted in order but after numerical data. You could try adding a column for the alphabetical element and sort on the numerical data (but include the alpha numerical date in the sort range) and that will be OK. Bill R |
#3
|
|||
|
|||
Sort list
"2039B" is text, and any text is treated by Excel as being greater than the
largest real number, that's why. To sort these like real numbers, use a helper col, eg in B1, copied down: =IF(A1="","",IF(ISNUMBER(A1),A1,LEFT(A1,4)+0)) Then sort both cols A and B by col B, ascending, and you should get the desired sort in col A. Success? hit YES below -- Max Singapore --- "janey" wrote: I have a table which I need to sort, e.g.: 2000 2039B 2026 2167 2239 2242 When I run Sort, I get 2000 2026 2167 2239 2242 2039B Why does 2039B come at the bottom rather than between 2026 and 2167? |
Thread Tools | |
Display Modes | |
|
|