Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to sort text with different lenght ?
Dear Sir,
I find that Excel 2002 could sort alphanumeric references of the same length very well but not the references with different number of character as illustrated below. Same Length Different Length (No problem in sorting) (Unable to sort in this sequence) FG0001 FG1 FG0012 FG12 FG0241 FG241 FG6806 FG6806 For references with different number of character, Excel could only group the same item together but not in ascending or descending order. Is there anyway for it to sort in sequence? Thanks Low -- A36B58K641 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to sort text with different lenght ?
Yes there is a way, not particularly 'handy', but there is a way.
The problem you've encountered is not Excel unique - it applies to any sorting of ASCII text that includes numeric characters. Just the nature of the beast. To sort your group, assuming they are in the format of the 'Different Length' examples: 2 alpha characters followed by from 1 to 4 numeric characters, total lenght desired always 6, then put this formula into a cell on the same row and fill it to the end of your list: =LEFT(A2,2) & REPT("0",6-LEN(A2)) & RIGHT(A2,LEN(A2)-2) In that example, A2 would be the cell with an entry like "FG1" or "FG241". Then to sort, include this helper column in the to-be-sorted range and use it as the key field to sort by. To explain the formula, first part: LEFT(A2,2) simply echos the 1st 2 characters in A2 - the 2 alpha characters. Second part: REPT("0",6-LEN(A2)) says repeat the character "0" 6-length of text in A2 times. Since we've agreed max length is to be 6, that's why 6 was used. When there's an entry like 'FG1' it returns 6-3 = 3 zeros, when 'FG1234' is in it, 6-6=0 zeros. If 'FG12345' then you get an error! Last part: RIGHT(A2,LEN(A2)-2) just echos all characters to the right of the 2 alpha characters. "Mr. Low" wrote: Dear Sir, I find that Excel 2002 could sort alphanumeric references of the same length very well but not the references with different number of character as illustrated below. Same Length Different Length (No problem in sorting) (Unable to sort in this sequence) FG0001 FG1 FG0012 FG12 FG0241 FG241 FG6806 FG6806 For references with different number of character, Excel could only group the same item together but not in ascending or descending order. Is there anyway for it to sort in sequence? Thanks Low -- A36B58K641 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to sort text with different lenght ?
You could turn your Different Length strings into Same Length strings
by means of a formula like this in a helper column: =LEFT(A1,2)&TEXT(RIGHT(A1,LEN(A1)-2),"0000") and copy down as needed. Alternatively, you could just extract the number part into a helper column with this: =VALUE(RIGHT(A1,LEN(A1)-2)) and copy this down. Either way, you then use the helper column as the sort key. Hope this helps. Pete On Jul 16, 2:34 pm, Mr. Low wrote: Dear Sir, I find that Excel 2002 could sort alphanumeric references of the same length very well but not the references with different number of character as illustrated below. Same Length Different Length (No problem in sorting) (Unable to sort in this sequence) FG0001 FG1 FG0012 FG12 FG0241 FG241 FG6806 FG6806 For references with different number of character, Excel could only group the same item together but not in ascending or descending order. Is there anyway for it to sort in sequence? Thanks Low -- A36B58K641 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to sort text with different lenght ?
Dear Sir,
Thanks for your detail explanation. Kind Regards Low -- A36B58K641 "JLatham" wrote: Yes there is a way, not particularly 'handy', but there is a way. The problem you've encountered is not Excel unique - it applies to any sorting of ASCII text that includes numeric characters. Just the nature of the beast. To sort your group, assuming they are in the format of the 'Different Length' examples: 2 alpha characters followed by from 1 to 4 numeric characters, total lenght desired always 6, then put this formula into a cell on the same row and fill it to the end of your list: =LEFT(A2,2) & REPT("0",6-LEN(A2)) & RIGHT(A2,LEN(A2)-2) In that example, A2 would be the cell with an entry like "FG1" or "FG241". Then to sort, include this helper column in the to-be-sorted range and use it as the key field to sort by. To explain the formula, first part: LEFT(A2,2) simply echos the 1st 2 characters in A2 - the 2 alpha characters. Second part: REPT("0",6-LEN(A2)) says repeat the character "0" 6-length of text in A2 times. Since we've agreed max length is to be 6, that's why 6 was used. When there's an entry like 'FG1' it returns 6-3 = 3 zeros, when 'FG1234' is in it, 6-6=0 zeros. If 'FG12345' then you get an error! Last part: RIGHT(A2,LEN(A2)-2) just echos all characters to the right of the 2 alpha characters. "Mr. Low" wrote: Dear Sir, I find that Excel 2002 could sort alphanumeric references of the same length very well but not the references with different number of character as illustrated below. Same Length Different Length (No problem in sorting) (Unable to sort in this sequence) FG0001 FG1 FG0012 FG12 FG0241 FG241 FG6806 FG6806 For references with different number of character, Excel could only group the same item together but not in ascending or descending order. Is there anyway for it to sort in sequence? Thanks Low -- A36B58K641 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: How to sort text with different lenght ?
Dear Sir,
Thanks for your helpful information. Best Regards Low -- A36B58K641 "Pete_UK" wrote: You could turn your Different Length strings into Same Length strings by means of a formula like this in a helper column: =LEFT(A1,2)&TEXT(RIGHT(A1,LEN(A1)-2),"0000") and copy down as needed. Alternatively, you could just extract the number part into a helper column with this: =VALUE(RIGHT(A1,LEN(A1)-2)) and copy this down. Either way, you then use the helper column as the sort key. Hope this helps. Pete On Jul 16, 2:34 pm, Mr. Low wrote: Dear Sir, I find that Excel 2002 could sort alphanumeric references of the same length very well but not the references with different number of character as illustrated below. Same Length Different Length (No problem in sorting) (Unable to sort in this sequence) FG0001 FG1 FG0012 FG12 FG0241 FG241 FG6806 FG6806 For references with different number of character, Excel could only group the same item together but not in ascending or descending order. Is there anyway for it to sort in sequence? Thanks Low -- A36B58K641 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002: How to add extension to text ? | Excel Discussion (Misc queries) | |||
repeating text Excel 2002 | Excel Discussion (Misc queries) | |||
Outlook 2002 calendar dates exported to Excel 2002 sort incorrectl | Excel Worksheet Functions | |||
Sort by date in Excel 2002? | Excel Discussion (Misc queries) | |||
Excel 2002 Will Not Print Text Boxes | Excel Discussion (Misc queries) |