Comma Seperated Values to Dataset

[Legacy 143009]

Hi,

I have comma separated values like below:
country1, code1, code2;
country2, code3, code4, code5;
.
.

What would be the best practice to convert to this:
Column A ColumnB
country1 code1
country1 code2
country2 code3
country2 code4
country2 code5

 

[JEC]

How about

Sub jec()
 Dim ar, sq, sp, j As Long, jj As Long, x As Long
 ar = Range("A1", Range("A" & Rows.Count).End(xlUp))
 ReDim sq(1, 0)
 
 For j = 1 To UBound(ar)
   sp = Split(ar(j, 1), ", ")
   For jj = 1 To UBound(sp)
     ReDim Preserve sq(1, x)
     sq(0, x) = sp(0)
     sq(1, x) = sp(jj)
     x = x + 1
   Next
 Next
 
 Range("A1").Resize(x, 2) = Application.Transpose(sq)
End Sub

 

[Legacy 143009]

Ok. I'll try tomorrow. I think VBA will be the best solution. I could write one but I was very busy with another code today. My eyes are leaking. Thanks for your time and effort!

 

[JEC]

You’re welcome!