Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel sort ( exclude letters include hyphens)?
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.worksheet.functions
|
|||
|
|||
Excel sort ( exclude letters include hyphens)?
When you say "then by value" do you mean the numerical value of
numbers to the right of the hyphen, or the value of those numbers to the left? In your example data, what would you expect the resulting order to be after sorting according to your rules? Pete On Jul 1, 4:14*pm, always confused <always wrote: 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.worksheet.functions
|
|||
|
|||
Excel sort ( exclude letters include hyphens)?
it should look like the list that I gave. so that anything after the hyphen
is ignored. and even though the numerical value of 002 is less than 05, 05 should come before 002 05-024 AB05-543 05-678 002-23 I hope I explained myself a little better. "Pete_UK" wrote: When you say "then by value" do you mean the numerical value of numbers to the right of the hyphen, or the value of those numbers to the left? In your example data, what would you expect the resulting order to be after sorting according to your rules? Pete On Jul 1, 4:14 pm, always confused <always wrote: 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.worksheet.functions
|
|||
|
|||
Excel sort ( exclude letters include hyphens)?
So, 05 is sorted before 002 because it has only 2 digits?
When you have (effectively, after ignoring the characters) 05-024, 05-543 and 05-678, these are sorted as text values (and just happen to be in numerical order as well)? I don't think you can just ignore the digits after the hyphen, otherwise you may end up with 05-543, 05-678 and 05-024. Pete On Jul 1, 5:09*pm, always confused wrote: it should look like the list that I gave. so that anything after the hyphen is ignored. and even though the numerical value of 002 is less than 05, 05 should come before 002 05-024 AB05-543 05-678 002-23 I hope I explained myself a little better. "Pete_UK" wrote: When you say "then by value" do you mean the numerical value of numbers to the right of the hyphen, or the value of those numbers to the left? In your example data, what would you expect the resulting order to be after sorting according to your rules? Pete On Jul 1, 4:14 pm, always confused <always wrote: 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?- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel sort ( exclude letters include hyphens)?
yes, because it only has 2 digits. do you know how I can do this?
"Pete_UK" wrote: So, 05 is sorted before 002 because it has only 2 digits? When you have (effectively, after ignoring the characters) 05-024, 05-543 and 05-678, these are sorted as text values (and just happen to be in numerical order as well)? I don't think you can just ignore the digits after the hyphen, otherwise you may end up with 05-543, 05-678 and 05-024. Pete On Jul 1, 5:09 pm, always confused wrote: it should look like the list that I gave. so that anything after the hyphen is ignored. and even though the numerical value of 002 is less than 05, 05 should come before 002 05-024 AB05-543 05-678 002-23 I hope I explained myself a little better. "Pete_UK" wrote: When you say "then by value" do you mean the numerical value of numbers to the right of the hyphen, or the value of those numbers to the left? In your example data, what would you expect the resulting order to be after sorting according to your rules? Pete On Jul 1, 4:14 pm, always confused <always wrote: 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?- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel sort ( exclude letters include hyphens)?
I only have your posted example data to go on, so assuming this is in
A1:A4 in any order, then put this formula in B1: =SUBSTITUTE(SUBSTITUTE(A1,"A",""),"B","") and this one in C1: =FIND("-",B1)-1 and copy these down to row 4. Then highlight all the data in A1:C4 and Data | Sort using column C as the first sort key and column B as the second sort key (no headers). This will put your data in the order you require them, and then you can delete the helper columns B and C. This assumes that you can only have the letters A and B in your codes, although the formula in column B could be extended to cater for up to 7 different letters. Hope this helps. Pete On Jul 1, 10:48*pm, always confused wrote: yes, because it only has 2 digits. *do you know how I can do this? "Pete_UK" wrote: So, 05 is sorted before 002 because it has only 2 digits? When you have (effectively, after ignoring the characters) 05-024, 05-543 and 05-678, these are sorted as text values (and just happen to be in numerical order as well)? I don't think you can just ignore the digits after the hyphen, otherwise you may end up with 05-543, 05-678 and 05-024. Pete On Jul 1, 5:09 pm, always confused wrote: it should look like the list that I gave. so that anything after the hyphen is ignored. and even though the numerical value of 002 is less than 05, 05 should come before 002 05-024 AB05-543 05-678 002-23 I hope I explained myself a little better. "Pete_UK" wrote: When you say "then by value" do you mean the numerical value of numbers to the right of the hyphen, or the value of those numbers to the left? In your example data, what would you expect the resulting order to be after sorting according to your rules? Pete On Jul 1, 4:14 pm, always confused <always wrote: 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?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Include/Exclude Holiday from Automatic Sheet Creation | Excel Discussion (Misc queries) | |||
Include ability to sort Excel cells by colour | Excel Worksheet Functions | |||
Use networkdays INCLUDE weekends, Exclude holidays | Excel Worksheet Functions | |||
Can auto number (or OFFSET) include letters? | Excel Worksheet Functions | |||
Does XL2K have "hard hyphens" (non-break hyphens)? | Excel Discussion (Misc queries) |