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.

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.

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

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

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

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

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