Hi JoAnn,

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

No. Sort is alphanumeric. Best you can do is;
Copy column 1,
Do Data -- Text to Columns using 'W' as a delimiter to split the data into
two numeric columns,
Sort on both the two new columns.

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.

This problem occurs because Excel stores some part numbers as Numeric and
some as text. Change the whole column to text and you should be ok.

To change the whole column to text, select the whole column, then use
Data -- text to columns, clear all delimiters, in step 3 choose Column data
format - Text.

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?

Ed Ferrero
Microsoft Excel MVP
http://www.edferrero.com