Sub SplitHyphenToComma()
Dim i, element
Dim rng As Range
Dim strPrefix As String
Dim strStartN As String
Dim strEndNum As String
Dim strConstr As String
Dim strSource As String
Dim arrConstr() As String
Dim arrSource() As String
For Each rng In Range("A2:A5")
strSource = rng.Value
rng.Offset(, 1) = ""
strConstr = ""
If InStr(strSource, ",") Then
arrSource = Split(strSource, ",")
For Each element In arrSource
If InStr(element, "-") Then
arrConstr = Split(element, "-")
strStartN = parseInt(arrConstr(0))
strPrefix = Replace(arrConstr(0), strStartN, "")
strEndNum = parseInt(arrConstr(1))
For i = strStartN To strEndNum
strConstr = strConstr & ", " & strPrefix & i
Next i
Else
strConstr = strConstr & ", " & element
End If
Next element
ElseIf InStr(strSource, ",") = 0 And InStr(strSource, "_") = 0 And InStr(strSource, "-") Then
arrConstr = Split(strSource, "-")
strStartN = parseInt(arrConstr(0))
strPrefix = Replace(arrConstr(0), strStartN, "")
strEndNum = arrConstr(1)
For i = strStartN To strEndNum
strConstr = strConstr & ", " & strPrefix & i
Next i
ElseIf InStr(strSource, "_") Then
arrSource = Split(strSource, "-")
arrConstr = Split(arrSource(0), "_")
strStartN = arrConstr(1)
strPrefix = arrConstr(0)
arrConstr = Split(arrSource(1), "_")
strEndNum = arrConstr(1)
For i = strStartN To strEndNum
strConstr = strConstr & ", " & strPrefix & "_" & i
Next i
End If
rng.Offset(, 1) = WorksheetFunction.Trim(Mid(strConstr, 2))
Next rng
End Sub
Private Function parseInt(x)
Dim i As Long, c As String, nums As String
nums = ""
For i = 1 To Len(x)
c = Mid(x, i, 1)
If IsNumeric(c) Then nums = nums & c
Next i
parseInt = nums
End Function