Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Alphanumeric Sort
I would like to know if there is a way to do an alphanumeric sort on some
data. I have item numbers like: 100 100A 200 300XX A current sort returns 100 200 100A 300XX Any Ideas? -- Ray Rohde |
#2
|
|||
|
|||
no can do.
100a is not a number as excel sees it. it's text excel sort numbers and text differently and when sorted togeather, number first, text last. built in. hard coded. if you set all of your other numbers to text, you might get the sort you are after. otherwise you might have to use a helper column. good luck HDR -----Original Message----- I would like to know if there is a way to do an alphanumeric sort on some data. I have item numbers like: 100 100A 200 300XX A current sort returns 100 200 100A 300XX Any Ideas? -- Ray Rohde . |
#3
|
|||
|
|||
Ray wrote:
I would like to know if there is a way to do an alphanumeric sort on some data. I have item numbers like: 100 100A 200 300XX A current sort returns 100 200 100A 300XX Any Ideas? Is this the sort order you're trying to get to? 100 100A 200 300XX The sort you showed is apparently the result of your column being some numbers and some text mixed in together. Make all the cells text and it sorts into the order that I've shown above. If you have a few cells you can edit them and place a ' at the beginning of the cell to force it to text. If you've got a bunch of them then you might create another column: [ ] = TEXT(A1,"########") This column will be text and you can either sort on that, or copy the new column and PasteSpecial/Values over top of the original data to force it to become text. Good luck.... Bill |
#4
|
|||
|
|||
Bill,
That is exactly what I was looking for. Thank you very much for your help Ray "Bill Martin -- (Remove NOSPAM from addre" wrote: Ray wrote: I would like to know if there is a way to do an alphanumeric sort on some data. I have item numbers like: 100 100A 200 300XX A current sort returns 100 200 100A 300XX Any Ideas? Is this the sort order you're trying to get to? 100 100A 200 300XX The sort you showed is apparently the result of your column being some numbers and some text mixed in together. Make all the cells text and it sorts into the order that I've shown above. If you have a few cells you can edit them and place a ' at the beginning of the cell to force it to text. If you've got a bunch of them then you might create another column: [ ] = TEXT(A1,"########") This column will be text and you can either sort on that, or copy the new column and PasteSpecial/Values over top of the original data to force it to become text. Good luck.... Bill |
#5
|
|||
|
|||
Been beating my head against sorting mixed item numbers for about 2 hours
myself; got the alphanumeric mix converted to text but that creates a new problem in that 121 sorts before 12, so I'm no better off. Idea? "Bill Martin -- (Remove NOSPAM from addre" wrote: Ray wrote: I would like to know if there is a way to do an alphanumeric sort on some data. I have item numbers like: 100 100A 200 300XX A current sort returns 100 200 100A 300XX Any Ideas? Is this the sort order you're trying to get to? 100 100A 200 300XX The sort you showed is apparently the result of your column being some numbers and some text mixed in together. Make all the cells text and it sorts into the order that I've shown above. If you have a few cells you can edit them and place a ' at the beginning of the cell to force it to text. If you've got a bunch of them then you might create another column: [ ] = TEXT(A1,"########") This column will be text and you can either sort on that, or copy the new column and PasteSpecial/Values over top of the original data to force it to become text. Good luck.... Bill |
#6
|
|||
|
|||
Breaking up the data into two columns will accomplish this task
If you add two helper columns to the left of your data the formula would be For the Numeric Portion: =Left(C1,3) For the Text: =MID(C1,4,5) Then sort on those two columns HTH PC "momsawake" wrote in message ... Been beating my head against sorting mixed item numbers for about 2 hours myself; got the alphanumeric mix converted to text but that creates a new problem in that 121 sorts before 12, so I'm no better off. Idea? "Bill Martin -- (Remove NOSPAM from addre" wrote: Ray wrote: I would like to know if there is a way to do an alphanumeric sort on some data. I have item numbers like: 100 100A 200 300XX A current sort returns 100 200 100A 300XX Any Ideas? Is this the sort order you're trying to get to? 100 100A 200 300XX The sort you showed is apparently the result of your column being some numbers and some text mixed in together. Make all the cells text and it sorts into the order that I've shown above. If you have a few cells you can edit them and place a ' at the beginning of the cell to force it to text. If you've got a bunch of them then you might create another column: [ ] = TEXT(A1,"########") This column will be text and you can either sort on that, or copy the new column and PasteSpecial/Values over top of the original data to force it to become text. Good luck.... Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort by row instead of by column? | Excel Worksheet Functions | |||
"-" ignored in sort | Excel Discussion (Misc queries) | |||
Excel Sort function should not sort the cell formatting! | Excel Worksheet Functions | |||
Keeping Row 1 at the top during a Data Sort... | Excel Worksheet Functions | |||
Data > Sort function amnesia? | Excel Discussion (Misc queries) |