Breakout one cell into various rows

[LVExcel]

I received this data (with thousands of records), where Tracking numbers are stored in a single cell and need a row for each tracking number with adjacent cell's data copied to each row.
My problem is that Tracking numbers have no space between them or comas in order to do Text To Column and then Transpose to rows.
Can someone please help me with this.

Tracking Nos	Cartons​	Wght	Freight
5845717537626 5845717538850 5845717539309 5845717537578​	4​	33.2​	65.34​
4845717973809 4845717970729 4845717976326 4845717976175 4845717977594 4845717979211​	6​	76.4​	155.84​
3846112383761 3846112385190 3846112387171​	3​	47.4​	62.33​

 

[mse330]

Try below VBA code assuming data starts in cell A1 and column B (Cartons) shows the total number of tracking numbers that are stored in a single cell in column A

Sub test()

Dim a
a = [a1].CurrentRegion.Value2
ReDim b(1 To [sum(b:b)], 1 To UBound(a, 2))

For x = 2 To UBound(a)
    For y = 0 To UBound(Split(a(x, 1), vbLf))
        i = i + 1
        For z = 2 To UBound(a, 2)
            b(i, 1) = Split(a(x, 1), vbLf)(y)
            b(i, z) = a(x, z)
        Next
    Next
Next

With Sheets.Add(, ActiveSheet)
    .[a1:d1] = [{"Tracking Nos","Cartons","Wght","Freight"}]
    .Columns(1).NumberFormat = "@"
    .[a2].Resize(i, UBound(b, 2)) = b
    .[a1].CurrentRegion.Columns.AutoFit
End With

End Sub

 

[LVExcel]

Try below VBA code assuming data starts in cell A1 and column B (Cartons) shows the total number of tracking numbers that are stored in a single cell in column A

Sub test()

Dim a
a = [a1].CurrentRegion.Value2
ReDim b(1 To [sum(b:b)], 1 To UBound(a, 2))

For x = 2 To UBound(a)
    For y = 0 To UBound(Split(a(x, 1), vbLf))
        i = i + 1
        For z = 2 To UBound(a, 2)
            b(i, 1) = Split(a(x, 1), vbLf)(y)
            b(i, z) = a(x, z)
        Next
    Next
Next

With Sheets.Add(, ActiveSheet)
    .[a1:d1] = [{"Tracking Nos","Cartons","Wght","Freight"}]
    .Columns(1).NumberFormat = "@"
    .[a2].Resize(i, UBound(b, 2)) = b
    .[a1].CurrentRegion.Columns.AutoFit
End With

End Sub

Hi Mse330, don’t know how you figured it out, but WOW!! that worked like a charm from the first try. Thanks so much, I will save it for future use on other files. I really appreciate this. Thanks for taking the time.

 

[mse330]

Glad I was able to help & thanks for the feedback