Splitting one column into serveral columns

[spittingfire]

Hi All,

I am looking for some help with splitting one column into several columns. I have attached with a sample of some data and will like to have each line on a separate column.

In row do I have included what I will like the data to look like (with no trailing spaces).

Any help with the will be most appreciated. I am open to either a VBA solution or excel formula solution.

Thanks again in advance.

[snb]

Did you try texttocolumns ? (ribbon / data / texttocolumns)

[spittingfire]

Thanks for the suggestion but that was the first and only thing I tried and results were not good.

[jonh]

Sub test()
    Const delim As String = "|"


    Dim r As Range: Set r = Range("a1", Range("a1").End(xlDown))
    
    For Each c In r
        c.Formula = "'" & Replace(c.Text, vbNewLine, delim)
    Next


    r.TextToColumns Destination:=Range("a1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=delim, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
        , 1), Array(13, 1), Array(14, 1)), TrailingMinusNumbers:=True
    
    r.RowHeight = 13
End Sub

[spittingfire]

Thanks jonh. That works nicely for me.