- Option Explicit
- Sub test()
- Dim arr, i, j, k, s, ss, t, p, pp, mark
- mark = ".0123456789"
- arr = Range("h10:h" & Cells(Rows.Count, "h").End(xlUp).Row).Value
- For i = 1 To UBound(arr, 1)
- t = Split(Replace(arr(i, 1), Space(1), vbNullString), ",")
- ReDim brr(1 To UBound(t) + 2, 1 To 3)
- For j = 0 To UBound(t)
- For k = Len(t(j)) To 1 Step -1
- If InStr(mark, Mid(t(j), k, 1)) = 0 Then
- brr(j + 1, 1) = t(j)
- brr(j + 1, 2) = left(t(j), k)
- brr(j + 1, 3) = Val(Mid(t(j), k + 1))
- Exit For
- End If
- Next
- Next
- Call bsort(brr, 1, UBound(brr, 1) - 1, 1, UBound(brr, 2), 2)
- For j = 1 To UBound(brr) - 1
- If brr(j, 2) <> brr(j + 1, 2) Then
- Call bsort(brr, p + 1, j, 1, UBound(brr, 2), 3)
- pp = p + 1
- For k = p + 2 To j + 1
- If brr(k, 3) - brr(k - 1, 3) <> 1 Then
- If k - pp > 1 Then ss = "-" & brr(k - 1, 1) Else ss = vbNullString
- s = s & "," & brr(pp, 1) & ss
- pp = k
- End If
- Next
- p = j
- End If
- Next
- arr(i, 1) = Mid(s, 2): p = 0: s = vbNullString
- Next
- [i10].Resize(UBound(arr, 1)) = arr
- End Sub
- Function bsort(arr, first, last, left, right, key)
- Dim i, j, k, t
- For i = first To last - 1
- For j = first To last + first - 1 - i
- If arr(j, key) > arr(j + 1, key) Then
- For k = left To right
- t = arr(j, k): arr(j, k) = arr(j + 1, k): arr(j + 1, k) = t
- Next
- End If
- Next
- Next
- End Function
复制代码 |