Help with sort in Excel 2000
I have a spreadsheet with a column of box numbers:
A21 A1407 A828 B7371 B986 H2490 E1082 E232 H267 When I sort the column I get: A1407 A21 A828 B7371 B986 E1082 E232 H2490 H267 What I want is: A21 A828 A1407 B986 B7371 E232 E1082 H267 H2490 No matter how I format the cells before I sort, I cannot get it to look like what I want. Apparently it has something to do with the default alpha numeric sort in excel?! Is there any way around it? Am I making sense? |
Assuming that the data is in column A starting in cell A2, I suggest you
create two "helper" columns at the (right hand) end of your data, say, columns X and Y. In cell X2, put: =LEFT(A2,1) In cell Y2, put =VALUE(RIGHT(A2,LEN(A2)-1)) Drag these two columns down to the end of your data. You'll get a #VALUE! Error if you drag too far. Now sort on columns X and Y. Finally, delete or hide columns X and Y Regards Trevor "TagKory" wrote in message ... I have a spreadsheet with a column of box numbers: A21 A1407 A828 B7371 B986 H2490 E1082 E232 H267 When I sort the column I get: A1407 A21 A828 B7371 B986 E1082 E232 H2490 H267 What I want is: A21 A828 A1407 B986 B7371 E232 E1082 H267 H2490 No matter how I format the cells before I sort, I cannot get it to look like what I want. Apparently it has something to do with the default alpha numeric sort in excel?! Is there any way around it? Am I making sense? |
If your data really looks like one alpha then lots of numerics, I'd use two
helper columns. =left(a1,1) and =--mid(a1,2,255) and drag down. Then sort all the data by the two helper columns. TagKory wrote: I have a spreadsheet with a column of box numbers: A21 A1407 A828 B7371 B986 H2490 E1082 E232 H267 When I sort the column I get: A1407 A21 A828 B7371 B986 E1082 E232 H2490 H267 What I want is: A21 A828 A1407 B986 B7371 E232 E1082 H267 H2490 No matter how I format the cells before I sort, I cannot get it to look like what I want. Apparently it has something to do with the default alpha numeric sort in excel?! Is there any way around it? Am I making sense? -- Dave Peterson |
All times are GMT +1. The time now is 09:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com