Splitting very large string separated with comma and i need to split 50 items only per row

im having very big string on 1st row.so 1st row contains lots of items with comma like below

12345,54322,44444,222222222,444444,121,333,44444,……..

I just need to split this till 50 items in every row. lets assume there are 700 items separated with comma and I want to keep till 50 items only in 1st row and then next 50 in 2nd row and so on.

I tried with the below code which splits till 50 for sure but im not sure if this will works going forward. so need help on this

OutData = Split(InpData, ",")(50)
MsgBox OutData

>Solution :

You can do this in many more ways, but one would be to replace every nth comma. For example through Regular Expressions:

Sub Test()

Dim s As String: s = "1,2,3,4,5,6,7,8,9,10,11"
Dim n As Long: n = 2
Dim arr() As String

With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "([^,]*(?:,[^,]*){" & n - 1 & "}),"
    arr = Split(.Replace(s, "$1|"), "|")
End With

End Sub

The pattern used means:

• ( – Open 1st capture group;
  • [^,]* – Match 0+ (Greedy) characters other than comma;
  • (?: – Open a nested non-capture group;
    • ,[^,]* – Match a comma and again 0+ characters other than comma;
    • ){1} – Close the non-capture group and match n-1 times (1 time in the given example);
  • ), – Close the capture group and match a literal comma.

Replace every match with the content of the 1st capture group and a character you know is not in the full string so we can split on that character. See an online demo

I suppose you can do whatever you like with the resulting array. You probably want to transpose it into the worksheet.