Home 
Search 
Today's Posts 
#1




AlphaNumeric Sorting
The ascend sort return my list as:
EXCEL SORT WOULD PREFER THIS R101 R11 R1010L R12 R102 R101 R11 R102 R111 R1010L R12 R111 R121 R121 I would prefer the sort from the second column. The second column was assembled manually. 
#2




AlphaNumeric Sorting
I don't think you can change the sorting options, but if you insert your
information like this it will sort the way you want. R0101 R0102 R1001 R1002 R1010L R1101 R1201 Another option would be to insert the information as time and use a custom format of R[h]m but you would have to change the one with the L to a custom format of R[h]mL Then it would look and sort the way you want. R11  Inserted as 1:01 R12  Inserted as 1:02 R101  Inserted as 10:01 R102  Inserted as 10:02 R1010L  Inserted as 10:10 with secondary format type R111  Inserted as 11:01 R121  Inserted as 12:01 "Jose" wrote: The ascend sort return my list as: EXCEL SORT WOULD PREFER THIS R101 R11 R1010L R12 R102 R101 R11 R102 R111 R1010L R12 R111 R121 R121 I would prefer the sort from the second column. The second column was assembled manually. 
#3




AlphaNumeric Sorting
On Mon, 5 Dec 2005 08:30:04 0800, "Jose"
wrote: The ascend sort return my list as: EXCEL SORT WOULD PREFER THIS R101 R11 R1010L R12 R102 R101 R11 R102 R111 R1010L R12 R111 R121 R121 I would prefer the sort from the second column. The second column was assembled manually. If I understand you correctly, you want to sort numerically first by the numbers that are between the "R" and the "", and then by the numbers that are after the "". The latter numbers may, on occasion, be followed by a letter. I would set up two "helper columns" in which you extract the sort keys. If your data is in Column F, then G1: =MID(F1,2,FIND("",F1)2) H1: =LOOKUP(9.99E+300,MID(F1,FIND("",F1)+1,ROW(INDIRECT("1:5")))) Select the entire range to be sorted, in this case F1:H7, and select Data/Sort and do an ascending sort first on column G and then on Column H. ron 
#4




AlphaNumeric Sorting
Thanks Ron, your recommendation was right on key. The issue was sorting
across the hyphen with what I had. Thanks all.  Regards "Ron Rosenfeld" wrote: On Mon, 5 Dec 2005 08:30:04 0800, "Jose" wrote: The ascend sort return my list as: EXCEL SORT WOULD PREFER THIS R101 R11 R1010L R12 R102 R101 R11 R102 R111 R1010L R12 R111 R121 R121 I would prefer the sort from the second column. The second column was assembled manually. If I understand you correctly, you want to sort numerically first by the numbers that are between the "R" and the "", and then by the numbers that are after the "". The latter numbers may, on occasion, be followed by a letter. I would set up two "helper columns" in which you extract the sort keys. If your data is in Column F, then G1: =MID(F1,2,FIND("",F1)2) H1: =LOOKUP(9.99E+300,MID(F1,FIND("",F1)+1,ROW(INDIRECT("1:5")))) Select the entire range to be sorted, in this case F1:H7, and select Data/Sort and do an ascending sort first on column G and then on Column H. ron 
#5




AlphaNumeric Sorting
Thanks Ron.
 Regards "Ron Rosenfeld" wrote: On Mon, 5 Dec 2005 08:30:04 0800, "Jose" wrote: The ascend sort return my list as: EXCEL SORT WOULD PREFER THIS R101 R11 R1010L R12 R102 R101 R11 R102 R111 R1010L R12 R111 R121 R121 I would prefer the sort from the second column. The second column was assembled manually. If I understand you correctly, you want to sort numerically first by the numbers that are between the "R" and the "", and then by the numbers that are after the "". The latter numbers may, on occasion, be followed by a letter. I would set up two "helper columns" in which you extract the sort keys. If your data is in Column F, then G1: =MID(F1,2,FIND("",F1)2) H1: =LOOKUP(9.99E+300,MID(F1,FIND("",F1)+1,ROW(INDIRECT("1:5")))) Select the entire range to be sorted, in this case F1:H7, and select Data/Sort and do an ascending sort first on column G and then on Column H. ron 
#6




AlphaNumeric Sorting
On Mon, 5 Dec 2005 12:59:02 0800, "Jose" wrote:
Thanks Ron, your recommendation was right on key. The issue was sorting across the hyphen with what I had. Thanks all.  Regards Glad it worked for you. Thanks for the feedback. ron 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
VLOOKUP WITH ALPHA NUMERIC  Excel Worksheet Functions  
Columns are now numeric, not alpha. how to change back?  Excel Discussion (Misc queries)  
Search string for alpha or numeric  Excel Worksheet Functions  
The colums changed from alpha to numeric how do you make it alpha  Excel Discussion (Misc queries)  
Alpha & Numeric Counts in Excel  Excel Discussion (Misc queries) 