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 array

reTxt = ""

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