#1   Report Post  
Bobbie
 
Posts: n/a
Default 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


  #2   Report Post  
akk
 
Posts: n/a
Default

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


.

  #3   Report Post  
Bobbie
 
Posts: n/a
Default

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" wrote in message
...
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


.





  #4   Report Post  
Gord Dibben
 
Posts: n/a
Default

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 SortColumn AAscending,
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"
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" wrote in message
...
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


.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Maintaining cell reference after sorting GRITS Excel Discussion (Misc queries) 2 April 30th 23 07:42 PM
Sorting protected worksheet Phyllis Excel Discussion (Misc queries) 3 December 16th 04 12:27 AM
sorting detail rows - summary row in an outline ExcelSteve Excel Discussion (Misc queries) 1 December 5th 04 03:40 PM
Sorting problem Klaus Excel Discussion (Misc queries) 3 December 4th 04 02:55 AM
sorting question Brian Excel Discussion (Misc queries) 4 November 28th 04 01:30 PM


All times are GMT +1. The time now is 03:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"