Here are 2 UDF that might help with the sort:
Extract numbers will capture the number in a string and ExtractAlpha
extracts the alpha value in a string.

Insert 2 blank columns in a copy of the your workbook, to the right of the
column that contains your list. In first blank column type
=ExtractNumbers(A1) and copy down to the last value in your list, in second
blank column type =ExtractAlpha(A1) and copy down to the last value in your
list. Then do a 2-way sort using the first formula column as sort parameter
1 and the second formula column as sort parameter 2.

To use the formulas, press Alt+F11 to open the VBE. Click INSERT on the
menu and select Module, then paste or type the 2 formulas below.

Function ExtractNumbers(varVal As Variant) As Long


Dim intLen As Integer
Dim strVal As String
Dim i As Integer
Dim strChar As String

intLen = Len(varVal)

For i = 1 To intLen
strChar = Mid$(varVal, i, 1)
If IsNumeric(strChar) Then strVal = strVal & strChar
Next i

If Len(strVal) = 0 Then
ExtractNumbers = 0
Else
ExtractNumbers = CLng(strVal)
End If

End Function

Function ExtractAlpha(varVal As Variant) As String

Dim intLen As Integer
Dim strVal As String
Dim i As Integer
Dim strChar As String

intLen = Len(varVal)

For i = 1 To intLen
strChar = Mid$(varVal, i, 1)
If Asc(strChar) = 65 And Asc(strChar) <= 90 Or _
Asc(strChar) = 97 And Asc(strChar) <= 122 Then
strVal = strVal & strChar
End If
Next i

ExtractAlpha = strVal

End Function


--
Kevin Backmann


"JDrumm" wrote:

Although this is similar to previous posts, it is different enough that
earlier solutions did not resolve my problem.

I am sorting a list of part numbers. They are in a three-part format --
prefix, base, suffix. The base is always numeric. The prefix and suffix are
alphabetic. A number may be comprised of one, two, or all three of the
sections. I want the numbers sorted by base first, then by prefix and then
by suffix. See a portion of my list below:

100MS
1577
200322
200861
200886S
202CCAG
40MPB
40MS
40MST
8984YY
90MS
AEL0801H
CDS211TTRW
DC216TTRB
DS206GG
P90R
PAWT214RA
PNR112R
WPS207GRC
WPSH115RRC

I want the 40xx numbers to come before 100MS and before 1157. Can this be
done in Excel 2003?

Thanks for any help you can give me.