There happens to be a similar work
Function expand character number (ra As Range) As String
'Dim ra As String
'ra = "A1-A2,A4,A5,A9-A12,B15,B8-B13"
Dim Txt As String, Tt As String
Dim Lengthtxt As Long
p>Dim i As Long, j As Long, No As Long
Dim reTxt As String
Dim arTx As Variant, oTxt() As String
< p>'Separate the text by delimiters, exit if there is no text, and save it to the otxt arrayreTxt = ""
arTx = Split(ra.Text, ", ")
If UBound(arTx) < 0 Then Exit Function
ReDim oTxt(UBound(arTx))
oTxt = arTx
'For i = 0 To UBound(arTx)
'oTxt(i) = arTx(i)
'Next i
'Loop array otxt comparison Whether each piece of text has the connector "-"
For j = 0 To UBound(oTxt)
'Debug.Print oTxt(j)
arTx = Split(oTxt(j), "-")
'The starting number before the connector can be directly added to the return string retxt
reTxt = reTxt & arTx(0) & ","
'If there are still characters after the - connector, add the middle number in a loop until it is equal to the last number
'First, check whether the first character of the number is consistent
' p>
If UBound(arTx) > 0 Then
Lengthtxt = Len(oTxt(j))
'Take the starting number character code as txt
< p>Txt = ""For i = 1 To Lengthtxt
Tt = Mid(arTx(0), i, 1)
Select Case Asc( Tt)
Case Asc("a") To Asc("z"), Asc("A") To Asc("Z")
Txt = Txt & Tt< /p>
Case Asc("0") To Asc("9")
Exit For
End Select
Next i
'Check that the character code of the ending number is consistent with the starting number, then add the middle number to the return string in a loop
If left(arTx(1), Len(Txt)) = Txt Then
No = Val(Replace(arTx(0), Txt, ""))
Do
No = No + 1
Tt = Txt & No
If Tt <> "" Then reTxt = reTxt & Tt & ","
Loop Until Tt = arTx(1)
End If
End If
Next j
'Output the returned string
'Debug.Print reTxt
If right(reTxt, 1) = "," Then reTxt = left(reTxt, Len(reTxt) - 1)
Expand character number = reTxt
End Function