Reformat single cell content into multiple with headers

Question

I have an order export from an online shop that export variables as a single cell in an .csv file.

tier: Elite | division: Male/Male Team | captain shirt size: Large | teammate shirt size: XLarge | Team Name: Terrible 2's | Participant/Volunteer Waiver & Liability Release   Captain's Full Name: Craig Carroll | Participant/Volunteer Waiver & Liability Release   Teammate's Full Name: Ashley Carroll | Waiver & Release of All Claims & Assumption of Risk   Captain's Intials: cc | Waiver & Release of All Claims & Assumption of Risk   Teammate's Initials: ac | Age Requirement: Yes

This is what it exports into one cell. Is there a way to make the word preceding the : become a header, as in A1, and the text preceding the | become the value in A2? I have played around with the text to columns feature but it creates cells formatted as tier:Elite and so on. Any suggestions for a solution for 400 orders or line items?

original

expectation

and so on

Answer 1

This macro assumes that the original data is in column A of Sheet1. The reorganized data will be placed in Sheet2:

Sub ReOrganize()
    Dim N As Long, i As Long, K As Long
    N = Cells(Rows.Count, "A").End(xlUp).Row
    K = 1

    For i = 1 To N
        ary = Split(Cells(i, "A").Value, "|")
        For Each a In ary
            bry = Split(a, ":")
            With Sheets("Sheet2")
                .Cells(K, 1).Value = bry(0)
                .Cells(K, 2).Value = bry(1)
                K = K + 1
            End With
        Next a
    Next i
End Sub

Answer 2

I would recommend the following steps:

• Use text to columns as you mentioned with | as the delimiter
• Then transpose the data down (highlight the row, copy, select a cell to paste to then edit special transpose)
• With the transposed data use text to columns again, this time with : as the delimiter

The data would end up in the format:

tier                 Elite 
division             Male/Male Team 
captain shirt size   Large 
teammate shirt size  XLarge 
Team Name            Terrible 2's
...

(Only showed the first five rows as there is a lot of text in the other rows)