| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I want sort a list but the sort is unlit on the home tab how do I | Excel Worksheet Functions | |||
| Sort list | Excel Discussion (Misc queries) | |||
| Sort with a List | Excel Worksheet Functions | |||
| Excel sort by Fill Color by custom list sort | Excel Discussion (Misc queries) | |||
| sort list of players by team from player list on separate sheet | Excel Worksheet Functions | |||
Linear Mode
