try something like this
Option Explicit
Sub SortStuff()
Dim rData As Range
Dim v As Variant
Dim r As Long, c As Long, i As Long, j As Long
Dim s As String
Dim sNewSheet As String
Application.ScreenUpdating = False
'make new name
sNewSheet = ActiveSheet.Name & "-Sorted"
'delete if it exists
Application.DisplayAlerts = False
On Error Resume Next
Worksheets(sNewSheet).Delete
On Error GoTo 0
Application.DisplayAlerts = True
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = sNewSheet
Set rData = ActiveSheet.Cells(1, 1).CurrentRegion
'get rid of empty strings
Call rData.Replace(vbNullString, "###", xlWhole)
Call rData.Replace("###", vbNullString, xlWhole)
With rData
' 'make into 00000skill format
For r = 2 To .Rows.Count
For c = 21 To 29
If Len(.Cells(r, c).Value) = 0 Then
.Cells(r, c).Value = "99999zzzzzzzzzz" ' force to end
Else
v = Split(.Cells(r, c).Value, ";")
If UBound(v) = 1 Then
.Cells(r, c).Value = Format(v(1), "00000") & v(0)
Else
.Cells(r, c).Value = "00001" & v(0)
End If
End If
Next c
Next r
'sort each row, 10 cells
For r = 2 To .Rows.Count
v = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(.Cells(r, 21).Resize(1, 9).Value))
'simple bubble sort
For i = LBound(v) To UBound(v) - 1
For j = i + 1 To UBound(v)
If v(i) > v(j) Then
s = v(i)
v(i) = v(j)
v(j) = s
End If
Next j
Next i
.Cells(r, 21).Resize(1, 9).Value = v
Next r
'make into skill;n format
For r = 2 To .Rows.Count
For c = 21 To 29
If .Cells(r, c).Value = "99999zzzzzzzzzz" Then
.Cells(r, c).Value = Empty
Else
.Cells(r, c).Value = Right(.Cells(r, c).Value, Len(.Cells(r, c).Value) - 5) & ";" & Format(Left(.Cells(r, c).Value, 5), "0")
End If
Next c
Next r
End With
Application.ScreenUpdating = True
MsgBox "Done"
End Sub