i would do as Dave said,and leave tose two helper columns in place with some
sort of fomulas that break the part number into parts and always sort by
those columns so any numbers that are added or appended are put into the
correct place.Hide them or have them over to the side so you dont ussually
see them
--
paul

remove nospam for email addy!



"Dave Peterson" wrote:

Can you use some helper columns (discard or hide them when you're done)???

If yes, then if column 1 is just the 1W followed by the numbers and nothing
else:

=left(a1,2)&text(mid(a1,3,3),"000")
and drag down.

Then sort the data by that helper column.

For the second column, I'd do the same kind of thing. I think your data just
sorts numbers before text. If you had preformatted the column as text, then
done the data entry, I think your data would sort the way you want.

You could convert your data to text by using a helper column:
=b1&""
(numbers will be converted to text and text won't change.)

or if you have leading 0's that are there because of formatting:
=text(b1,"00000000")
drag down and sort by this helper column.

Be aware that just changing the format of a cell is not enough to change the
value (to text). You have to do more (Hitting F2, then enter would be enough
for one cell--after the format was changed to text.)

And one more warning.

When you sort data that contains hyphens, you may be surprised that excel
ignores them in the sort sequence.

From xl2003's help for "Troubleshoot sorting"

Alphanumeric sort When you sort alphanumeric text, Excel sorts left to right,
character by character. For example, if a cell contains the text "A100," Excel
places the cell after a cell that contains the entry "A1" and before a cell that
contains the entry "A11."

Text and text that includes numbers are sorted in the following order:

0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^
_ ` { | } ~ + < = A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Apostrophes (') and hyphens (-) are ignored, with one exception: If two text
strings are the same except for a hyphen, the text with the hyphen is sorted
last.


"JoAnn Paules [MVP]" wrote:

Excel 2003

I am working on a spreadsheet at work and would like to be able to change
the sorting order easily. There are two columns I'd like to switch around.

Column 1 is a list of part designators. They can begin with 1W, 2W, or 3W
and are followed by up to a 3 digit number. When I try to sort now, it puts
1W10 before 1W2. I *cannot* change the designator to 1W002. Is there a way
to format that column so that it will yield 1W2 before 1W10 (followed by
2Wnnn and 3Wnnn).

Column 2 are part numbers that are based on part drawing numbers. The
drawing numbers are generally an 8-digit number, however (you saw this one
coming, didn't you?), some of the part numbers are followed with a dash and
a number. For example, I may have part numbers 12345678, 12345679, 12234567,
12234567-1. I would like to be able to sort this column so that it looked at
all of the characters as a group. Right now it puts any number with a dash
at the end of the column.

For now I have it sorted manually but it's a pain in the butt because
sometimes I have a part number and sometimes I have a designator number.
Yes, I know I can use the "Find" feature but I like sorting. I actually have
two worksheets going so if I change one, I need to change the other.

Is there help for me or is this one of those things that I need to learn to
live with?

--

JoAnn Paules
MVP Microsoft [Publisher]

--

Dave Peterson