(Please forgive the noobie questions. I'm much better with Publisher than I
am with Excel.)
The delimiter is the dash and I need that. If I get rid if the dash, then
12345678-1 becomes 123456781, doesn't it?
I fear I may be in over my head with this idea.
--
JoAnn Paules
MVP Microsoft [Publisher]
"Ed Ferrero" wrote in message
...
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