Got it, let's figure out why Excel's messing up the sort on those alphanumeric values and fix it. The core problem is that Excel treats these strings as plain text, so it sorts them character-by-character instead of recognizing the numeric segment at the end. For example, CTP315-07-220 would land before CTP315-07-51 in default text sorting because "2" is lex smaller than "5"—totally not the numeric order you want. Here are three solid solutions, plus a fix for your existing script if you need it:

1. Helper Column (Quickest & Most User-Friendly) #

This is the go-to for one-off tasks—no coding required:

• Insert a new column right next to your data (say your values are in Column A, use Column B as the helper)
• In cell B1, paste this formula to extract and convert the last segment to a number:

  =VALUE(RIGHT(A1,LEN(A1)-FIND("-",A1,FIND("-",A1)+1)))
  

  Breakdown: FIND("-",A1,FIND("-",A1)+1) locates the second hyphen, then we calculate the length of the final segment, extract it with RIGHT, and convert it to a numeric value with VALUE.
• Drag the formula down to apply it to all rows
• Select both your original data column and the helper column, go to Data > Sort
• Set the sort key to your helper column, choose ascending/descending order, and hit OK
• Once sorted, you can hide or delete the helper column if you don't need it anymore

2. Custom VBA Function (For Reusable, Column-Free Sorting) #

If you want to avoid helper columns and have a reusable tool, a VBA function works great:

• Press Alt + F11 to open the VBA Editor
• Click Insert > Module to create a new module
• Paste this code into the module:

  Function GetLastNumericSegment(cell As Range) As Double
      Dim valueParts() As String
      valueParts = Split(cell.Value, "-")
      GetLastNumericSegment = CDbl(valueParts(UBound(valueParts)))
  End Function
  

• Go back to your Excel sheet, select the range you want to sort
• Open the Data > Sort dialog
• Under "Sort by", pick your data column, then set "Sort On" to Custom Formula (in newer Excel versions) and enter =GetLastNumericSegment(A1) (adjust A1 to match your first data cell)
• Choose your desired sort order and click OK—this will sort based on the numeric value of the final segment

3. Power Query (For Bulk/Repeatable Processing) #

If you work with this type of data regularly, Power Query is perfect because it’s repeatable and scalable:

• Select your data range, then go to Data > From Table/Range (make sure your data has a header row)
• In the Power Query Editor, click Add Column > Custom Column
• Paste this formula into the custom column input box (replace YourDataColumn with your actual column name):

  = Number.FromText(List.Last(Splitter.SplitTextByDelimiter("-")([YourDataColumn])))
  

• Click OK to create the column, then go to Home > Sort > Sort Ascending (or Descending) using the new custom column
• Hit Close & Load to bring the sorted data back into Excel

Bonus: Fixing Your Existing Script #

If your current script isn’t working, it’s likely not properly extracting the numeric segment or applying the sort logic correctly. Here’s a quick VBA sub that handles the sort properly:

Sub SortByFinalNumericSegment()
    Dim targetSheet As Worksheet
    Set targetSheet = ActiveSheet
    Dim dataRange As Range
    Set dataRange = targetSheet.Range("A1:A" & targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row)
    
    ' Add temporary helper column
    targetSheet.Columns("B").Insert
    targetSheet.Range("B1:B" & dataRange.Rows.Count).Formula = "=VALUE(RIGHT(A1,LEN(A1)-FIND(""-"",A1,FIND(""-"",A1)+1)))"
    
    ' Sort the combined range
    targetSheet.Range("A1:B" & dataRange.Rows.Count).Sort _
        Key1:=targetSheet.Range("B1"), _
        Order1:=xlAscending, _
        Header:=xlYes ' Change to xlNo if your data doesn't have a header row
    
    ' Clean up helper column
    targetSheet.Columns("B").Delete
End Sub

Just adjust the range (A1:A...) to match your actual data, and tweak the Header setting as needed.

内容的提问来源于stack exchange，提问作者Kaladin Stormblessed