Excel Sorting
 

I need to know how to sort a group of numbers that have a different amount of
digits in them and may have dashes and letters. I have tried formatting the
cells as numbers, text, and general. None of that works. It always comes up
with the 3 digit numbers first, then 4 digit, then 5 etc. I need to be able
to sort it by 1st digit, then 2nd, then 3rd, etc.

Example

112
1134-blk
112563
1290-wht
1345
150
80-17068-03

Excel Sorting
 

First, create a helper column to truly transform the data into text:
=TEXT(A2,"@")

Next, select both columns, and go to Data - Sort. Sort the helper column
Ascending, and at next dialogue, choose "Sort numbers and numbers stored as
text seperately". Will produce:

112
112563
1134-blk
1290-wht
1345
150
80-17068-03


--
Best Regards,

Luke M
"Judy" wrote in message
...
I need to know how to sort a group of numbers that have a different amount
of
digits in them and may have dashes and letters. I have tried formatting
the
cells as numbers, text, and general. None of that works. It always comes
up
with the 3 digit numbers first, then 4 digit, then 5 etc. I need to be
able
to sort it by 1st digit, then 2nd, then 3rd, etc.

Example

112
1134-blk
112563
1290-wht
1345
150
80-17068-03

Excel Sorting
 

OK I assumed that the formula should have the "A2" increasing as you move
down the rows?

"Luke M" wrote:

First, create a helper column to truly transform the data into text:
=TEXT(A2,"@")

Next, select both columns, and go to Data - Sort. Sort the helper column
Ascending, and at next dialogue, choose "Sort numbers and numbers stored as
text seperately". Will produce:

112
112563
1134-blk
1290-wht
1345
150
80-17068-03


--
Best Regards,

Luke M
"Judy" wrote in message
...
I need to know how to sort a group of numbers that have a different amount
of
digits in them and may have dashes and letters. I have tried formatting
the
cells as numbers, text, and general. None of that works. It always comes
up
with the 3 digit numbers first, then 4 digit, then 5 etc. I need to be
able
to sort it by 1st digit, then 2nd, then 3rd, etc.

Example

112
1134-blk
112563
1290-wht
1345
150
80-17068-03



.

Excel Sorting
 

On 3/17/2010 11:08 AM, Judy wrote:
OK I assumed that the formula should have the "A2" increasing as you move
down the rows?

Yes, Luke's formula assumes the first number is in cell A2 with the
other numbers following below.

Bill

Excel Sorting
 

I cannot get it to fill down while increasing the number. What is the
secret? I tried the things that I normally do without success.

"Billns" wrote:

On 3/17/2010 11:08 AM, Judy wrote:
OK I assumed that the formula should have the "A2" increasing as you move
down the rows?

Yes, Luke's formula assumes the first number is in cell A2 with the
other numbers following below.

Bill
.