Aplpha Numeric Mixed Sorting

[Guest]

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.

 

[Guest]

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.

 

[Dave Peterson]

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.

 

[Guest]

Dave,
Even when text, they didn't sort as OP requested

 

[Dave Peterson]

I don't understand. I got this order when I sorted:

853TT00123
8540000587
90000HH223
9150000001
915000000X

What did you get?

 

[Bernard Liengme]

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

 

[Dave Peterson]

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.)