sorting dilema

  • Thread starter Thread starter Bobbie
  • Start date Start date
B

Bobbie

I'm trying to sort a worksheet by part numbers. The problem is some part
numbers have a letter at the end, such as 'U' for unstamped, and those sort
totally separate from the orginal part number. Any way around this? Here's
an example to clarify.
Part #s sort this way
000004
000110
304114
000004R
000110U
304114U

I'd like them to sort
000004
000004R
000110
000110U
304114
304114U

The cells are Text format so the leading zeros are displayed.


Thanks in advance
Bobbie
 
Hi

you get this problem while sorting only if some of the
cells are in number format and others in text format. If
all the cells sorted are in text format, sorting produces
the results as desired by you.
 
Great! I'll change it tomorrow when I'm at work. It seems like the 000004R
and 000110U would have to be Text already or the leading zeros wouldn't
display but from what you say I'll bet they're not. Will be interesting to
see what format they are before I change them over.

Thanks for the fix!
Bobbie
 
Bobbie

If the cells with numbers only are not formatted to Custom 000000 then they
would also be text and your sort should work.

In Excel 2002 with all cells as text, when I click on Sort>Column A>Ascending,
I am given the choice of "sort anything that looks like a number, as a number"
and "sort text and numbers separately".

Select the second option and you get a sort as you wish.

Gord Dibben Excel MVP
 
Back
Top