sorting dilema

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.


>-----Original Message-----
>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
>
>
>.
>

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

"akk" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
> >-----Original Message-----
> >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
> >
> >
> >.
> >

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

On Tue, 4 Jan 2005 21:44:30 -0800, "Bobbie" <[email protected]>
wrote:

>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
>
>"akk" <[email protected]> wrote in message
>news:[email protected]...
>> 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.
>>
>>
>> >-----Original Message-----
>> >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
>> >
>> >
>> >.
>> >
>
>
>