| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Aplpha Numeric Mixed Sorting
I have some part numbers. They are all 10 digits. But it could be a pure 10
digit number like "9150000001", but also could be any kind of mix up of apha and numeric like "853TT00123", or "A00008299X". I want them to be sorted like this way: 853TT00123 8540000587 90000HH223 9150000001 915000000X What I meant is that it sort digit by digit - It sort by the first digit, then the 2nd digit, then the 3rd... then the 10th. Thanks in advance. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Aplpha Numeric Mixed Sorting
You will have to put your data into 10 columns and then do several sorts,
starting with right most columns (8,9,10), then columns 5,6,7 etc. At the end concatenate to get back to your original part numbers. "plumstone" wrote: I have some part numbers. They are all 10 digits. But it could be a pure 10 digit number like "9150000001", but also could be any kind of mix up of apha and numeric like "853TT00123", or "A00008299X". I want them to be sorted like this way: 853TT00123 8540000587 90000HH223 9150000001 915000000X What I meant is that it sort digit by digit - It sort by the first digit, then the 2nd digit, then the 3rd... then the 10th. Thanks in advance. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Aplpha Numeric Mixed Sorting
Always 10 characters?
Maybe you could use a helper column with formulas like: =a2&"" (headers in row 1, then copy down the range as far as required) To force the entries to be text. plumstone wrote: I have some part numbers. They are all 10 digits. But it could be a pure 10 digit number like "9150000001", but also could be any kind of mix up of apha and numeric like "853TT00123", or "A00008299X". I want them to be sorted like this way: 853TT00123 8540000587 90000HH223 9150000001 915000000X What I meant is that it sort digit by digit - It sort by the first digit, then the 2nd digit, then the 3rd... then the 10th. Thanks in advance. -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Aplpha Numeric Mixed Sorting
Dave,
Even when text, they didn't sort as OP requested "Dave Peterson" wrote: Always 10 characters? Maybe you could use a helper column with formulas like: =a2&"" (headers in row 1, then copy down the range as far as required) To force the entries to be text. plumstone wrote: I have some part numbers. They are all 10 digits. But it could be a pure 10 digit number like "9150000001", but also could be any kind of mix up of apha and numeric like "853TT00123", or "A00008299X". I want them to be sorted like this way: 853TT00123 8540000587 90000HH223 9150000001 915000000X What I meant is that it sort digit by digit - It sort by the first digit, then the 2nd digit, then the 3rd... then the 10th. Thanks in advance. -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Aplpha Numeric Mixed Sorting
I don't understand. I got this order when I sorted:
853TT00123 8540000587 90000HH223 9150000001 915000000X What did you get? Toppers wrote: Dave, Even when text, they didn't sort as OP requested "Dave Peterson" wrote: Always 10 characters? Maybe you could use a helper column with formulas like: =a2&"" (headers in row 1, then copy down the range as far as required) To force the entries to be text. plumstone wrote: I have some part numbers. They are all 10 digits. But it could be a pure 10 digit number like "9150000001", but also could be any kind of mix up of apha and numeric like "853TT00123", or "A00008299X". I want them to be sorted like this way: 853TT00123 8540000587 90000HH223 9150000001 915000000X What I meant is that it sort digit by digit - It sort by the first digit, then the 2nd digit, then the 3rd... then the 10th. Thanks in advance. -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Aplpha Numeric Mixed Sorting
Interesting: Using your &" " method I get the same as you have below. There
was a dialog box asking how entries that seem to be numbers should be treated and I specified "as text" If you just format the range as text, you do not get the required sort - numbers are separated from alpha entries. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Dave Peterson" wrote in message ... I don't understand. I got this order when I sorted: 853TT00123 8540000587 90000HH223 9150000001 915000000X What did you get? Toppers wrote: Dave, Even when text, they didn't sort as OP requested "Dave Peterson" wrote: Always 10 characters? Maybe you could use a helper column with formulas like: =a2&"" (headers in row 1, then copy down the range as far as required) To force the entries to be text. plumstone wrote: I have some part numbers. They are all 10 digits. But it could be a pure 10 digit number like "9150000001", but also could be any kind of mix up of apha and numeric like "853TT00123", or "A00008299X". I want them to be sorted like this way: 853TT00123 8540000587 90000HH223 9150000001 915000000X What I meant is that it sort digit by digit - It sort by the first digit, then the 2nd digit, then the 3rd... then the 10th. Thanks in advance. -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Aplpha Numeric Mixed Sorting
But formatting the cells as text isn't enough to change the values in the cells.
(And I actually used &"" (no space character) to convert the non-text to text. A very minor point that wouldn't change the results in this example.) Bernard Liengme wrote: Interesting: Using your &" " method I get the same as you have below. There was a dialog box asking how entries that seem to be numbers should be treated and I specified "as text" If you just format the range as text, you do not get the required sort - numbers are separated from alpha entries. best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Dave Peterson" wrote in message ... I don't understand. I got this order when I sorted: 853TT00123 8540000587 90000HH223 9150000001 915000000X What did you get? Toppers wrote: Dave, Even when text, they didn't sort as OP requested "Dave Peterson" wrote: Always 10 characters? Maybe you could use a helper column with formulas like: =a2&"" (headers in row 1, then copy down the range as far as required) To force the entries to be text. plumstone wrote: I have some part numbers. They are all 10 digits. But it could be a pure 10 digit number like "9150000001", but also could be any kind of mix up of apha and numeric like "853TT00123", or "A00008299X". I want them to be sorted like this way: 853TT00123 8540000587 90000HH223 9150000001 915000000X What I meant is that it sort digit by digit - It sort by the first digit, then the 2nd digit, then the 3rd... then the 10th. Thanks in advance. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sorting Alpha-Numeric Values | Charts and Charting in Excel | |||
| Extract just numeric part of mixed text/number entry? | Excel Worksheet Functions | |||
| Sorting Alpha Numeric | Excel Discussion (Misc queries) | |||
| Alpha-Numeric Sorting | Excel Discussion (Misc queries) | |||
| Sorting mixed up linked cells in a workbook? | Excel Worksheet Functions | |||
Linear Mode
