Split by text and add weeks

[tobermory]

Hello there,

I have a column that indicates weeks where a class is taking place. However, instead of listing each individual week number, it groups them together. For instance, 20-28 is weeks 20,21,22,23,24,25,26,27,28.

The column has instances of multiple groupings, separated by comma. I include a screenshot below. I'm looking to convert the groupings into individual weeks.

Column N is my own manual working, where I've manually converted the weeks into the output I am looking for. Would there be a combination of formulas, beyond my knowledge, that could take the manual work out of this process?

Note - Some rows are just individual week numbers and do not contain either a comma or dash.

20-28, 33-35
20-24, 26-28, 33-34

Thanks in advance!

Tobermory

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[JEC]

Here a VBA solution

Sub jec()
 Dim ar, it, sp, x, j As Long, jj As Long
 ar = Range("A1", Range("A" & Rows.Count).End(xlUp))
 
 For j = 1 To UBound(ar)
   For Each it In Split(ar(j, 1), ",")
     sp = Split(it, "-")
     If UBound(sp) = 0 Then Exit For
     For jj = sp(0) To sp(1)
        x = x & IIf(x = "", "", ",") & jj
     Next
     ar(j, 1) = x
   Next
   x = ""
 Next

 Range("A1", Range("A" & Rows.Count).End(xlUp)) = ar
End Sub

 

[Fluff]

If you have 365 with TEXTSPLIT, a formula option

Fluff.xlsm
	L	M
1
2	20	20
3	21	21
4	20-28, 33-35	20,21,22,23,24,25,26,27,28,33,34,35
5	20-24, 26-28, 33-34	20,21,22,23,24,26,27,28,33,34
6	20-28, 33-35	20,21,22,23,24,25,26,27,28,33,34,35
7	20-28	20,21,22,23,24,25,26,27,28
Main

Cell Formulas
Range	Formula
M2:M7	M2	=IF(ISNUMBER(FIND("-",L2)),REDUCE("",TEXTSPLIT(L2,",",,1),LAMBDA(a,b,LET(x,TEXTSPLIT(b,"-",,1)+0,TEXTJOIN(",",,a,SEQUENCE(INDEX(x,1,2)-INDEX(x,1,1)+1,,INDEX(x,1,1)))))),L2)

 

[Fluff]

Not sure if you could have a situation like L6, but this will handle it if you do.

Fluff.xlsm
	L	M
1
2	20	20
3	21	21
4	20-28, 33-35	20,21,22,23,24,25,26,27,28,33,34,35
5	20-24, 26-28, 33-34	20,21,22,23,24,26,27,28,33,34
6	20-28, 30, 33-35	20,21,22,23,24,25,26,27,28,30,33,34,35
7	20-28	20,21,22,23,24,25,26,27,28
Main

Cell Formulas
Range	Formula
M2:M7	M2	=REDUCE("",TEXTSPLIT(L2,",",,1),LAMBDA(a,b,LET(x,TEXTSPLIT(b,"-",,1)+0,TEXTJOIN(",",,a,IF(COLUMNS(x)=1,x,SEQUENCE(INDEX(x,1,2)-INDEX(x,1,1)+1,,INDEX(x,1,1)))))))