Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort letters before numbers in Excel? | Excel Discussion (Misc queries) | |||
Return a digit in a string of numbers | Excel Discussion (Misc queries) | |||
Numbers won't sort correctly. | Excel Discussion (Misc queries) | |||
Numbers won't sort correctly. | Excel Discussion (Misc queries) | |||
Tell users how to sort 5 digit and 9 digit zipcodes correctly aft. | New Users to Excel |