Option Explicit
Function TextSort(s As String) As String
If s = "" Then Exit Function
Dim ary
ary = Split(s, ",")
Dim ary2()
ReDim ary2(UBound(ary), 1)
Dim i As Long
For i = 0 To UBound(ary)
ary2(i, 0) = ary(i)
ary2(i, 1) = extractNum(ary(i))
Next
With WorksheetFunction
ary2 = .Sort(ary2, 2, 1, False)
TextSort = .TextJoin(",", True, .Index(ary2, 0, 1))
End With
End Function
Function extractNum(s) As Long
Dim i As Long, pos1 As Long, Pos2 As Long, flg As Boolean
For i = 1 To Len(s)
If Not flg And IsNumeric(Mid(s, i, 1)) Then
flg = True
pos1 = i
ElseIf flg And Not IsNumeric(Mid(s, i, 1)) Then
Exit For
End If
Next
Pos2 = i
If pos1 > 0 Then extractNum = Val(Mid(s, pos1, Pos2 - pos1))
End Function
Function mySort(s As String) As String
Dim sl As Object
Dim re As Object
Dim ary As Variant
Dim ary2 As Variant
Dim num As Long
Dim e As Variant
Dim k As Long
Set sl = CreateObject("System.Collections.SortedList")
Set re = CreateObject("VBScript.RegExp")
re.Pattern = "^.*?(\d+).*?$"
ary = Split(s, ",")
num = UBound(ary) + 1
ReDim ary2(1 To num) As String
For Each e In ary
sl.Add CLng(re.Replace(e, "$1")), e
Next
For k = sl.Count - 1 To 0 Step -1
ary2(num - k) = sl.GetByIndex(k)
Next
mySort = Join(ary2, ",")
End Function
