Excel sorting macro help

I need Excel to do more than it's capable of doing without creating a VB macro, and I haven't written a program since my BASIC class in college.

I have a list of materials that I need to sort by alphanumeric reference designator (C1, CR5, R10, U2, etc.). However, Excel's alphanumeric sort is a character-by-character sort, which results in numbering that is out of sequence (C1, C10, C100, C101, C102, C103, etc.). What I think I need to do is parse the alpha characters to a separate column, then do a 2-column sort in Excel. The problem is that some reference designators have more than one alpha character, so I can't just do a "Text to Columns" split. Can anyone help me with some coding ideas?

This is quite a big job to explain but your task is do able in excel97

here isa quick go at it which will help you:

insert a column next to the column that holds your key field
fill with code like this:

=IF(MID(A1,2,1)>"a","2nd chr is letter","2nd character is number")& " and length is " &LEN(A1)

this should output:
c1 2nd character is number and length is 2
cr5 2nd chr is letter and length is 3
cr10 2nd chr is letter and length is 4
cr7 2nd chr is letter and length is 3

you could break this down into numbers in coulmns:
data numChr length txtpart numpart
c1 2 2 c 1
cr5 3 3 cr 5
cr10 3 4 cr 10
cr700 3 5 cr 700

where the calculation columns have the following codes:
numChr
=IF(MID(A16,2,1)>"a","3","2")
length
=LEN(A16)
txtpart
=LEFT(A16,B16-1)
numpart
=VALUE(MID(A16,B16,C16-(B16-1)))

the value function takes the txt string from the mid function and makes it into a number - which fixes the 10, before 2 text sorting problem.

i then highlighted all the data and selected data menu - sort option. i sorted by txtpart and then numprt ascending and it came out like this:

Data: numChr length txtpart numpart
c1 2 2 c 1
cr5 3 3 cr 5
cr10 3 4 cr 10
cr700 3 5 cr 700

i am attaching the spreadsheet i did the work in for you to peruse.

you'll have to copy the above technique into new columns for all your spreadsheets data and then sort the data and then hide the work columns.

post here if you need more help or if there is an error in my solution.

Hope this helps.

This will parse your cells by looking at the values character by character, when it finds the first number in the value, then it puts everything to the right of the first number (including the first number) to the adjacent column. Assumes the parsed cells are in column A, without any blank rows. If it has been a while, it may be worth looking up functions like Left, Mid, etc in the online documentation.
Dim rngRange As Range
Dim intI As Integer
Dim intJ As Integer
'set the range
Range("A1:A1").Select
Set rngRange = Range(Selection, Selection.End(xlDown))
For intI = 1 To rngRange.Rows.Count
intJ = 0
While intJ <= Len(Cells(intI, 1).Value)
intJ = intJ + 1
'look at the string, character by character
If Mid(Cells(intI, 1).Value, intJ, 1) Like "#" Then
'if number, copy to adjacent column
Cells(intI, 2).Value = Right(Cells(intI, 1).Value, Len(Cells(intI, 1).Value) - intJ + 1)
Cells(intI, 1).Value = Left(Cells(intI, 1).Value, intJ - 1)
End If
Wend
Next intI