I'm having a problem sorting columns in excel?

Question

I am trying to use excel to keep a record of inventory I use for my business. In column A is the manufactures part number. The problem is they will not sort correctly. I googled a few different solutions but nothing worked correctly. no matter what I do the column is sorted like this:
100AM
1000F
1004H
101AM
1045
997B
A1001F
A1114E
CH3
L1054G
I hope you get the idea.

This is what I would like it to look like
CH3
100AM
101AM
997B
1000F
A1001F
1004A
1045
L1054G
A1114E

Is there a way this can be done? I would appreciate any help. Thank you. Tim

Answer 1

Probably not the most efficient way, but you can create a user-defined function to extract the number in another column, then sort by the new column. Here is how to do it: with Excel open, press ALT+F11, then click Insert, and choose Module. Paste the code below at the spot where the cursor is. Close the VBA window. You can now use your new function



=GetNum(A2)



where A2 is the cell to extract the number from. Copy down the column, then sort by this column. Save the file as a macro-enabled workbook.



Function GetNum(Mixed As String)

For i = 1 To Len(Mixed)

Test = Mid(Mixed, i, 1)

If IsNumeric(Test) Then

GetNum = GetNum & Test

End If

Next i

End Function

Answer 2

Won't happen your way, you are going to have to manually list them. The sort is designed more as an ascending/descending method for sorting - I don't have a clue what kind of sort you are going for, and certainly Excel wouldn't be able to figure it out.



edit/correction - I figured out that you are sorting by numbers only - you would have to remove text to recognize the numbers only. Let me suggest this - do three separate columns for your serial numbers - first column is the letters in front of the serial numbers, second column the number only and third column the ending letters. Group those three columns together by lightly shading them so they are easier to read and format the columns to not allow any space between them, so the number are separated enough from the remainder of the spreadsheet. That should solve your problem.





And try doing the custom list - see if that works with what you are trying to do

http://office.microsoft.com/en-us/excel-help/sort-data-in-a-range-or-table-HP010073947.aspx#BMsort_by_a_custom_list

Answer 3

sophisticated factor. seek on google or bing. this may help!