Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sort
I'm trying to use the sort comand on excel so that my list looks like this
05-024 AB05-543 05-678 002-23 What was asked of me is to sort the list so that letters are not taken into consideration and so that it is sorted by the number of digits to the left of the hyphen then by value. Does anyone know how to do this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sort
With your sample data in A1:A4, enter the following UDF in a normal Module:
Function sNum(g As Range) Application.Volatile f = g.Value For i = 1 To Len(f) If Mid(f, i, 1) Like "[A-Z]" Then GoTo skip If Mid(f, i, 1) = "-" Then Exit For sNum = sNum & Mid(f, i, 1) skip: Next i End Function Then in B1 enter: =LEN(sNum(A1))&sNum(A1) and copy down to B4 In C1 enter: =MID(A1,FIND("-",A1)+1,255) And copy down to C4. Now sort by Column B then by Column C -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "always confused" wrote in message ... I'm trying to use the sort comand on excel so that my list looks like this 05-024 AB05-543 05-678 002-23 What was asked of me is to sort the list so that letters are not taken into consideration and so that it is sorted by the number of digits to the left of the hyphen then by value. Does anyone know how to do this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sort
I guess my list is a ittle more complicated than I though. It started to
work and went wrong somewhere. thank yo for your help though. "Sandy Mann" wrote: With your sample data in A1:A4, enter the following UDF in a normal Module: Function sNum(g As Range) Application.Volatile f = g.Value For i = 1 To Len(f) If Mid(f, i, 1) Like "[A-Z]" Then GoTo skip If Mid(f, i, 1) = "-" Then Exit For sNum = sNum & Mid(f, i, 1) skip: Next i End Function Then in B1 enter: =LEN(sNum(A1))&sNum(A1) and copy down to B4 In C1 enter: =MID(A1,FIND("-",A1)+1,255) And copy down to C4. Now sort by Column B then by Column C -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "always confused" wrote in message ... I'm trying to use the sort comand on excel so that my list looks like this 05-024 AB05-543 05-678 002-23 What was asked of me is to sort the list so that letters are not taken into consideration and so that it is sorted by the number of digits to the left of the hyphen then by value. Does anyone know how to do this? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sort
You have a response at your earlier post. Perhaps you can post further
examples of your data. Pete "always confused" wrote in message ... I guess my list is a ittle more complicated than I though. It started to work and went wrong somewhere. thank yo for your help though. "Sandy Mann" wrote: With your sample data in A1:A4, enter the following UDF in a normal Module: Function sNum(g As Range) Application.Volatile f = g.Value For i = 1 To Len(f) If Mid(f, i, 1) Like "[A-Z]" Then GoTo skip If Mid(f, i, 1) = "-" Then Exit For sNum = sNum & Mid(f, i, 1) skip: Next i End Function Then in B1 enter: =LEN(sNum(A1))&sNum(A1) and copy down to B4 In C1 enter: =MID(A1,FIND("-",A1)+1,255) And copy down to C4. Now sort by Column B then by Column C -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "always confused" wrote in message ... I'm trying to use the sort comand on excel so that my list looks like this 05-024 AB05-543 05-678 002-23 What was asked of me is to sort the list so that letters are not taken into consideration and so that it is sorted by the number of digits to the left of the hyphen then by value. Does anyone know how to do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort function for dates does not sort properly in Office 2007 Exc. | Excel Worksheet Functions | |||
data, sort option is grayed. how to sort on a column? | Excel Discussion (Misc queries) | |||
Ascending Sort formula, change to neg #: descending sort.. | Excel Discussion (Misc queries) | |||
Excel sort by Fill Color by custom list sort | Excel Discussion (Misc queries) | |||
Excel Sort function should not sort the cell formatting! | Excel Worksheet Functions |