Convert single line of data into multiple lines of Excel data

Question

I have a database which has been corrupted. The data has been outputted as a CSV, but it only has the data in a single line. Each data record doesn't delineate other than an occasional semicolon.

I'd like to find each semicolon, then select everything from the next cell to the end, cut and paste to the start of the next line, then start again (as a cyclical macro until no more instances of the semicolon are found).

Here's some sample data: Company name, company phone, street address, address details, suburb/city, state, post code, telephone, trading hours, customer contact ";" (and repeat... a few thousand times).

Here is VBA code I tried:

Sub findnext()
    Range("A1").Select
    Cells.Find(What:=";", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Cut
    Columns("A").Find("", Cells(Rows.Count, "A")).Select
    ActiveSheet.Paste
End Sub

Answer 1

try this approach

Sub test()
    Dim SplitTranspose, x%
    SplitTranspose = Split([A1], ";")
    For x = 0 To UBound(SplitTranspose)
        Cells(x + 1, 1) = SplitTranspose(x)
    Next
End Sub

output result