| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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? |
|
#2
|
|||
|
|||
|
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? |
|
#3
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Microsoft Excel 2003 and Hyperion Retrieve with Excel 2000. | Excel Discussion (Misc queries) | |||
| Formula Integrity Not Preserved During Sort in Excel 2000 | Excel Discussion (Misc queries) | |||
| other systems detecting excel 4.0 if excel 2000 is installed | Setting up and Configuration of Excel | |||
| How do I Break links Using Excel 2000 | Excel Discussion (Misc queries) | |||
| Statistical Excel Function Question within Excel 2000... | Excel Worksheet Functions | |||
Linear Mode
