Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort alphanumeric
I cant seem to sort 25th 10th 30th 21st 2nd etc entered data to display as
2nd 10th 21st 25th 30th in descending . All cells have been formated as text before data entered. I am using Excel 2003 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort alphanumeric
Hi Arran
Try using a helper column. In the helper column, enter =--(LEFT(A1,LEN(A1)-2)) and copy down as far as required. Mark both columns and sort by the helper column. -- Regards Roger Govier "Arran" wrote in message ... I cant seem to sort 25th 10th 30th 21st 2nd etc entered data to display as 2nd 10th 21st 25th 30th in descending . All cells have been formated as text before data entered. I am using Excel 2003 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort alphanumeric
Roger, many thanks that worked like a dream.
"Roger Govier" wrote: Hi Arran Try using a helper column. In the helper column, enter =--(LEFT(A1,LEN(A1)-2)) and copy down as far as required. Mark both columns and sort by the helper column. -- Regards Roger Govier "Arran" wrote in message ... I cant seem to sort 25th 10th 30th 21st 2nd etc entered data to display as 2nd 10th 21st 25th 30th in descending . All cells have been formated as text before data entered. I am using Excel 2003 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort alphanumeric
You're very welcome. Thanks for the feedback.
-- Regards Roger Govier "Arran" wrote in message ... Roger, many thanks that worked like a dream. "Roger Govier" wrote: Hi Arran Try using a helper column. In the helper column, enter =--(LEFT(A1,LEN(A1)-2)) and copy down as far as required. Mark both columns and sort by the helper column. -- Regards Roger Govier "Arran" wrote in message ... I cant seem to sort 25th 10th 30th 21st 2nd etc entered data to display as 2nd 10th 21st 25th 30th in descending . All cells have been formated as text before data entered. I am using Excel 2003 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort alphanumeric
Hoping this thread is not yet dead as I would like to expand on it.
What changes are required to the formula below, so that when there is just text (abcde etc) rather than alpanumeric (25th) in A12 that I get the desired "0" returned rather than"#Value" that I am getting. =IF(A120,--(LEFT(A12,LEN(A12)-2)),0) Taking this one step further. There will be random blank cells in ColA so what would have to be added to the formula so that the sorted list is displayed from the top of the sorted range down rather than the bottom up. Help is always humbly received "Roger Govier" wrote: You're very welcome. Thanks for the feedback. -- Regards Roger Govier "Arran" wrote in message ... Roger, many thanks that worked like a dream. "Roger Govier" wrote: Hi Arran Try using a helper column. In the helper column, enter =--(LEFT(A1,LEN(A1)-2)) and copy down as far as required. Mark both columns and sort by the helper column. -- Regards Roger Govier "Arran" wrote in message ... I cant seem to sort 25th 10th 30th 21st 2nd etc entered data to display as 2nd 10th 21st 25th 30th in descending . All cells have been formated as text before data entered. I am using Excel 2003 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort alphanumeric
Hi Arran
Try the following =IF(A1="",REPT("Z",255),IF(ISNUMBER(--LEFT(A1)),--(LEFT(A1,LEN(A1)-2)),A1)) This will make empty cells have 255 Z's in the helper column, and they will sort to the end of the list Text cells will be left exactly as they are. Is this what you wanted? If you want the blank cells to the top of the list, then change the "REPT("Z",255) to 0 -- Regards Roger Govier "Arran" wrote in message ... Hoping this thread is not yet dead as I would like to expand on it. What changes are required to the formula below, so that when there is just text (abcde etc) rather than alpanumeric (25th) in A12 that I get the desired "0" returned rather than"#Value" that I am getting. =IF(A120,--(LEFT(A12,LEN(A12)-2)),0) Taking this one step further. There will be random blank cells in ColA so what would have to be added to the formula so that the sorted list is displayed from the top of the sorted range down rather than the bottom up. Help is always humbly received "Roger Govier" wrote: You're very welcome. Thanks for the feedback. -- Regards Roger Govier "Arran" wrote in message ... Roger, many thanks that worked like a dream. "Roger Govier" wrote: Hi Arran Try using a helper column. In the helper column, enter =--(LEFT(A1,LEN(A1)-2)) and copy down as far as required. Mark both columns and sort by the helper column. -- Regards Roger Govier "Arran" wrote in message ... I cant seem to sort 25th 10th 30th 21st 2nd etc entered data to display as 2nd 10th 21st 25th 30th in descending . All cells have been formated as text before data entered. I am using Excel 2003 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort alphanumeric | Excel Discussion (Misc queries) | |||
alphanumeric sort | Excel Discussion (Misc queries) | |||
sort alphanumeric data | New Users to Excel | |||
Sort - alphanumeric. | New Users to Excel | |||
Alphanumeric Sort | Excel Discussion (Misc queries) |