2000 item numbers in a column to a delimited row

[IsaacR]

I have 2000 item numbers in a column that I was able to get into a delimited row with commas. I was wondering how to break these into groups of 30 item numbers.

I was using =TEXTJOIN(", ",,A1:A30)
=TEXTJOIN(", ",,A31:A60) ETC. It works great but super tedious. Any suggestions?
Thank you

 

[JoeMo]

You could use VBA. Below assumes items are in col A starting in cell A2. This puts all the items into a comma-delimited list in cell C2.

Sub Isaac()
Dim V As Variant, S As String
V = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value  'change range to suit
S = Join(Application.Transpose(V), ",")
Range("C2").Value = S
End Sub

 

[Akuini]

@JoeMo I think you missed this part:

I was wondering how to break these into groups of 30 item numbers.

 

[Rick Rothstein]

Does this formula give you what you want...

=BYROW(WRAPROWS(A1:A2000,30),LAMBDA(r,TEXTJOIN(", ",,r)))

 

[Peter_SSs]

Does this formula give you what you want...

It doesn't for me - because 2,000 rows is not a multiple of 30 so a #N/A value appears in the final row instead of the final 20 values.
This would fix it though

=BYROW(WRAPROWS(A1:A2000,30,""),LAMBDA(r,TEXTJOIN(", ",,r)))

This one also worked for me.

=TEXTSPLIT(TEXTJOIN(TEXTSPLIT(REPT(", .",29)&"|","."),1,A1:A2000),,"|")

 

[IsaacR]

Does this formula give you what you want...

=BYROW(WRAPROWS(A1:A2000,30),LAMBDA(r,TEXTJOIN(", ",,r)))

It did, thank you very much

 

[Rick Rothstein]

It did, thank you very much

Did you see Peter_SSs' reply to my post (Message #5) in which he specified a value ("") for an optional argument that I accidentally omitted which will suppress #N/A errors at the bottom of the list if the columns are not fully populated?