Fandt selv en løsning på det + gennemløb af alle Rows.

 

Sub separatecsvdata()
 Dim k As Long
 Dim l As Long
 Dim mydata As String
 Dim mydata1 As String
 
'count number of rows
 k = Cells(Rows.Count, "A").End(xlUp).Row
 Application.ScreenUpdating = False
'run through every row with data
 For i = 1 To k
'define a variable that will hold the row-index or column-index values
 y = 2
'count number of characters in actual data set
 l = Len(Cells(i, 1))
'read data set in row
 mydata1 = Cells(i, 1)
'ad comma in the end of data set
 Cells(i, 1) = Left(mydata1, (l + 1)) & ","
'start a loop and let it run as long as there is data in the cell actual row
 Do While Cells(i, 1) <> ""
'assign the data in cell a1 to the variable my data
 mydata = Cells(i, 1)
'find out the position of the comma
 mycomma = InStr(mydata, ",")
'to ensure that the data that is left-over does not have a comma is also worked upon by the program and shifted appropriately
 If mycomma = 0 Then
' the end data, goes to the next empty column
 Cells(i, y) = Cells(i, 1)
'finally empty the column, so that you now know that all the comma separated data has been properly transferred to different cells
 Cells(i, 1) = ""
 'now goto 10
 GoTo 10
 End If
'the left function extracts the number of characters from the left upto the comma and then removes the comma
 Cells(i, y) = Left(mydata, (mycomma - 1))
'the mid function extracts the characters after the comma in our case
 Cells(i, 1) = Mid(mydata, (mycomma + 1))
'go to the next column
 y = y + 1
 Loop
10
'go to the next row
 Next
 Application.ScreenUpdating = True
 End Sub