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

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

CodePudding user response：

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.