Part Number Sorting..... Help :)

[renojimmyjam]

I have tried everything I can think of to accomplish this sort. At this time I have spent more time trying to figure out the sorting of my document than it would have taken to re type it.

Here is my problem:

I have part numbers that are either solid numbers or alphanumeric. When Excel sorts the numbers it sorts the raw numbers and then starts to sort the alpha numeric numbers from left to right.

EG:

192
750
752
775
998
1001
1004
1005
1007
1001A
1001ABM
1001E
1001EB
1009A
100AM
1010N
1011D1
1011D41

<tbody>
</tbody>

Notice that the 100AM is sorted after the 1009A and should be sorted before the 192 at the top.

Thanks for any help I could get on this one.

 

[diddi]

hi and welcome to the forum

assume data in column A
if you made a helper column ("B") containing =VALUE(A1)
that would give numeric part, and that sort both columns on col B

 

[Teethless mama]

Assuming your data start in A2:Axxx

B2: =IF(ISNUMBER(A2),A2,LOOKUP(1E+99,--LEFT(A2,ROW($1:$99))))

C2: =SUBSTITUTE(A2,B2,"")

Select B2:C2 and copy down as far as needed


Select A2:Cxxx and sort column B, then column C. Delete both columns B & C after you finished sorting.

 

[renojimmyjam]

Hey your awesome!! Teethless, But I still have one other problem that I found.

Half of my part numbers start with a letter also. Your method worked perfect for the numbers with aft letters but it gives an error with the pre letters. Is there a solution to this problem? Thanks Again!!

1639	1639
1640	1640
1761B	1761	B
A1000DA	#N/A	#N/A
A1001BH	#N/A	#N/A
A1001C2	#N/A	#N/A
A1001DH	#N/A	#N/A
A1001EH	#N/A	#N/A
A1011-5N	#N/A	#N/A
A1011D1	#N/A	#N/A

<colgroup><col style="width:48pt" width="64"> <col style="width:48pt" width="64"> <col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

 

[renojimmyjam]

Also to complicate the problem some of the part numbers contain TWO letters if the front.

X1055JK

X999

XL7

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>