Thanx Gord,
You are great.
I was wondering if you can explain to me how the formula(=text(A1,"000000")
work ?And why 6 zeroes (000000) and not more or less?
Thanx again

"Gord Dibben" wrote:

Each post brings up more wrinkles<g

Assume you have followed the previous steps and Column A is all you have
left.

In B1 enter =TEXT(A1,"000000")

Copy down.

Sort on column B and select "sort numbers and numbers stored as text
separately"


Gord


On Mon, 21 Jul 2008 10:53:01 -0700, peyman
wrote:

Hi Gord,
Thank you so much for your reply but it didn't work for me completey.the
result is when I sort both the columns as per your advise, the part numbers
with dash come before the part number without dash number like below:
...
938333 011
938333 013
938333
...
Any solution to bring up the 938333 before the two above to have :
...
938333
938333 011
938333 013
...
Thanx again.

"Gord Dibben" wrote:

Aha!

Larger example<g

Excel sorts numbers before text.

The numbers with the dashes are text.

Your first example was just a coincedence that it sorted properly.

Assuming data is in A1:Axx

Some work to do but DataText to ColumnsDelimted byOther type a dash and
NextSelect right hand column and Column Data FormatText and Finish.

Now sort both columns together.

In C1 enter =IF(B1<"",A1&"-"&B1,A1) and copy down

Copy column C and Paste SpecialValues onto Column A

Delete column B

Hopefully someone will come up with a few less steps.


Gord


On Sat, 19 Jul 2008 09:59:00 -0700, peyman
wrote:

Sorry Gord,
Assume there are more Part numbers.I'm adding them to the list to show you
where the problem is:
100321
103528
205999
100321-001
103528-011
205999-011
205999-051
pl705280
pl705283
I get the above list when the list is sorted but the desired sort should be
like below:
100321
100321-001
103528
103528-011
205999
205999-011
205999-051
pl705280
pl705283
Thanx


"Gord Dibben" wrote:

I am confused.

The sort order example you just posted is the same as the sort order you
requested in the "like below" example in your original post.

Post an example of what you think the order should be, not what you are
currently getting.


Gord

On Sat, 19 Jul 2008 08:46:09 -0700, peyman
wrote:

Thanx Gord,
I'm also using Excel2003 but when I sort it, first the numbers line up and
then the numbers with dash numbers and at the end the part numbers starting
with letters like below:
103528
205999
205999-011
205999-051
pl705280
pl705283

Any suggestions?
Peiman




"Gord Dibben" wrote:

Some of the numbers are text so you can't formatcellsnumbers

Your example input data sorts into your example output data in my Excel 2003

What results are you getting?


Gord Dibben MS Excel MVP

On Fri, 18 Jul 2008 15:27:01 -0700, peyman
wrote:

Hi,
I've got a list of part numbers like:
205999
pl705283
pl705280
205999-011
103528
205999-051
How can I sort this column to have the part nos. like below?Should I change
the "format cells/numbers" for the cells of this column:
sorted coulmn:
103528
205999
205999-011
205999-051
pl705280
pl705283
Thanx for ur help.
Peiman