Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Andyd
 
Posts: n/a
Default sort numbers based on initial digit.

running excell 2002.
I have a list of part numbers starting 1xxxxxxxxx (8 digits total) and
ending 9(4 digits). Default will put the larger number of digits last but I
need the first number of the sequence to be the primary sort number. eg:
12345678, 2345, 6, 789, 8, 99999. etc.
Any simple solution?
  #2   Report Post  
Biff
 
Posts: n/a
Default

Hi!

One way....

Use a helper column and extract the first 8 digits from the p/n then sort on
that column.

Assume p/n's are in the range A1:A100:

A1 = 12345678, 2345, 6, 789, 8, 99999

Say the helper column is column D.

In D1 enter this formula:

=LEFT(A1,8)*1

This will extract the NUMBER 12345678

Copy down as needed

Then convert all those helper formulas to constants by selecting them and
then doing a Copy/Paste Special/Values.

Select the range to sort and sort on column D.

When done get rid of the helper values.

Biff

"Andyd" wrote in message
...
running excell 2002.
I have a list of part numbers starting 1xxxxxxxxx (8 digits total) and
ending 9(4 digits). Default will put the larger number of digits last but
I
need the first number of the sequence to be the primary sort number. eg:
12345678, 2345, 6, 789, 8, 99999. etc.
Any simple solution?



  #3   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default

You would get that sort order automatically if the "numbers" were text
digits instead of numbers. If there is no reason that they need to be
numeric, then in an empty column use a formula like =A1&"" to coerce to
text. Then copy and Edit|Paste Special|Values back into the desired
location.

Jerry

Andyd wrote:

running excell 2002.
I have a list of part numbers starting 1xxxxxxxxx (8 digits total) and
ending 9(4 digits). Default will put the larger number of digits last but I
need the first number of the sequence to be the primary sort number. eg:
12345678, 2345, 6, 789, 8, 99999. etc.
Any simple solution?


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
How do I sort letters before numbers in Excel? RiverGirl Excel Discussion (Misc queries) 4 May 27th 05 04:09 PM
Return a digit in a string of numbers W M Excel Discussion (Misc queries) 5 May 11th 05 06:51 PM
Numbers won't sort correctly. FernW Excel Discussion (Misc queries) 10 April 1st 05 01:29 PM
Numbers won't sort correctly. FernW Excel Discussion (Misc queries) 1 March 3rd 05 03:46 PM
Tell users how to sort 5 digit and 9 digit zipcodes correctly aft. [email protected] New Users to Excel 1 February 18th 05 01:59 AM


All times are GMT +1. The time now is 11:56 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"