transpose and expand

[Danny54]

How would I go about splitting by the comma, transpose to column b, then expand the values between the dash?

Input sheet

New Sheet

Thanks

 

[Joe4]

Try this:

Sub MyMacro()

    Dim str1 As String
    Dim arr1() As String
    Dim arr2() As String
    Dim i As Long
    Dim r As Long
    Dim mn As Long
    Dim mx As Long
    Dim j As Long
    
    Application.ScreenUpdating = False
    
'   Designate range of values
    str1 = Range("A1").Value
    
'   Create array of values, separated by comma
    arr1 = Split(str1, ",")
    
'   Loop through each value in array
    For i = LBound(arr1) To UBound(arr1)
'       Check to see if there is a range in the value
        If InStr(1, arr1(i), "-") > 0 Then
'           Split values
            arr2 = Split(arr1(i), "-")
'           Populate values in column B
            mn = arr2(0)
            mx = arr2(1)
            For j = mn To mx
                r = r + 1
                Cells(r, "B") = j
            Next j
        Else
'           Populate single value in column B
            r = r + 1
            Cells(r, "B") = arr1(i)
        End If
    Next i
          
    Application.ScreenUpdating = True
    
End Sub

 

[Danny54]

Perfect.

Thanks so much

 

[Joe4]

You are welcome.
Glad I was able to help.

 

[smozgur]

An alternative UDF version:

Function EXPANDANDTRANSPOSE(rng As Range)
Dim arrCommaSep As Variant
Dim itemVal1 As Variant
Dim arrDashSep As Variant
Dim itemVal2 As Variant
Dim i As Integer
Dim tmpCollection As New Collection
Dim tmpArray() As Integer

    If rng.Cells.Count > 1 Then
        EXPANDANDTRANSPOSE = CVErr(xlErrValue)
    Else
        arrCommaSep = Split(rng, ",")
        For Each itemVal1 In arrCommaSep
            arrDashSep = Split(itemVal1, "-")
            tmpCollection.Add arrDashSep(0)
            If UBound(arrDashSep) Then
                For i = 1 To arrDashSep(UBound(arrDashSep)) - arrDashSep(0)
                    tmpCollection.Add arrDashSep(0) + i
                Next i
            End If
        Next itemVal1
    
        ReDim tmpArray(0, 1 To tmpCollection.Count)
        For i = 1 To tmpCollection.Count
            tmpArray(0, i) = tmpCollection.Item(i)
        Next i
    
        EXPANDANDTRANSPOSE = Application.WorksheetFunction.Transpose(tmpArray)
    End If
End Function

Book1
	A	B
1	1-3, 5, 7, 9,12-14,17,350-354	1
2		2
3		3
4		5
5		7
6		9
7		12
8		13
9		14
10		17
11		350
12		351
13		352
14		353
15		354
Sheet1

Cell Formulas
Range	Formula
B1:B15	B1	=EXPANDANDTRANSPOSE(A1)
Dynamic array formulas.

 

[JamesCanale]

I made a VBA free solution. But I couldn't boil it all down to one formula, because the formula got too long for excel to handle. I'll bet the LET function could make quick work of it - I'd love to see that:

MrExcelPlayground4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Input	Output		First , or -	Last , or -	first number	last number	total sequence	split by comma	number of groups (+1 needed)		Unfiltered solution			min for dash groups	max for dash groups		filtered dash min	filtered dash max		count of dash groups	widest dash group		array of dash group				vector of dash group
2	1-3,5,7,9,12-14,17,350-352	1		2	23	1	352	1	1-3	6		1			1	3		1	3		3	3		1	2	3		1
3		2						2	5			2			#VALUE!	#VALUE!		12	14					12	13	14		2
4		3						3	7			3			#VALUE!	#VALUE!		350	352					350	351	352		3
5		5						4	9			0			#VALUE!	#VALUE!												12
6		7						5	12-14			5			12	14												13
7		9						6	17			0			#VALUE!	#VALUE!												14
8		12						7	350-352			7			350	352												350
9		13						8				0																351
10		14						9				9																352
11		17						10				0
12		350						11				0
13		351						12				12
14		352						13				13
15								14				14
16								15				0
17								16				0
18								17				17
19								18				0
20								19				0
21								20				0
22								21				0
23								22				0
24								23				0
25								24				0
26								25				0
27								26				0
28								27				0
29								28				0
30								29				0
31								30				0
32								31				0
33								32				0
34								33				0
35								34				0
36								35				0
37								36				0
38								37				0
Sheet18

Cell Formulas
Range	Formula
B2:B14	B2	=FILTER(L2#,L2#>0)
D2	D2	=MIN(SEARCH("-",A2),SEARCH(",",A2))
E2	E2	=MAX(IFERROR(SEARCH("-",A2,SEQUENCE(LEN(A2))),0),IFERROR(SEARCH(",",A2,SEQUENCE(LEN(A2))),0))
F2	F2	=VALUE(LEFT(A2,D2-1))
G2	G2	=VALUE(RIGHT(A2,LEN(A2)-E2))
H2:H353	H2	=SEQUENCE(G2-F2+1,1,F2,1)
I2:I8	I2	=TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",999)),SEQUENCE(J2+1)*999-998,999))
J2	J2	=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))
L2:L353	L2	=IF(NOT(ISERROR(MATCH(H2#,--I2#,0))),H2#,(--NOT(ISNA(MATCH(H2#,AB2#,0))))*H2#)
O2:O8	O2	=VALUE(LEFT(I2#,SEARCH("-",I2#)-1))
P2:P8	P2	=VALUE(RIGHT(I2#,LEN(I2#)-SEARCH("-",I2#)))
R2:S4	R2	=FILTER(O2#,ISNUMBER(VALUE(O2#)))
U2	U2	=ROWS(R2#)
V2	V2	=MAX(S2#-R2#)+1
X2:Z4	X2	=SEQUENCE(U2,V2,0,0)+R2#+SEQUENCE(1,V2,0,1)*(--(SEQUENCE(U2,V2,0,0)+R2#+SEQUENCE(1,V2,0,1)<=S2#))
AB2:AB10	AB2	=SEQUENCE(ROWS(X2#)*COLUMNS(X2#),1,1,0)*INDEX(X2#,INT((SEQUENCE(ROWS(X2#)*COLUMNS(X2#))-1)/COLUMNS(X2#))+1,MOD(SEQUENCE(ROWS(X2#)*COLUMNS(X2#))-1,COLUMNS(X2#))+1)
Dynamic array formulas.

Without the LET, the array in X will spill if the biggest 'dash' group is more than 3.