sort numbers based on initial digit.

[Guest]

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?

 

[Biff]

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

 

[Jerry W. Lewis]

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