Excel data into one column

Question

I work for a charity and not too familiar with excel.

The data is is imported from our website into a spreadsheet but the orders are not separated into rows and columns as I would like. Is it possible to arrange multiple data in rows into one column? Not every row has the same number of entries.I have used text to columns to convert csv data into columns.

Thank you

Edit

This is how the import appears on the csv file:

Orders Product Name 50, 30 water aid, food parcel 5 general 20 medical 1, 5, 20, 100 general, water aid, medical, food

AND This is what I would like it to look like.

Orders Product name 50 water aid 30 food parcel 5 general 20 medical 1 general 5 water aid 20 medical 100 food

Really not sure how i can import the csv format here, anyone help?

Answer 1

VBA Macro solves the issue:

How it works:

• Press Alt+F11 to open VB editor.
• Copy & Paste this code as standard module.

---

    Sub SplitData()

    Dim xRg As Range
    Dim xRg1 As Range
    Dim xCell As Range
    Dim i As Long
    Dim xAddress As String
    Dim xUpdate As Boolean
    Dim xRet As Variant

    On Error Resume Next

    xAddress = Application.ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select a column", "Split Data", xAddress, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)

    If xRg Is Nothing Then Exit Sub

        If xRg.Columns.Count > 1 Then
            MsgBox "You can't select multiple columns", , "Split Data"
            Exit Sub
            End If

            Set xRg1 = Application.InputBox("Split to (select single cell):", "Split Data", , , , , , 8)
            Set xRg1 = xRg1.Range("A1")

            If xRg1 Is Nothing Then Exit Sub
                xUpdate = Application.ScreenUpdating
                Application.ScreenUpdating = False

                For Each xCell In xRg
                    xRet = Split(xCell.Value, ",")
                    xRg1.Worksheet.Range(xRg1.Offset(i, 0), xRg1.Offset(i + UBound(xRet, 1), 0)) = Application.WorksheetFunction.Transpose(xRet)
                    i = i + UBound(xRet, 1) + 1
                Next
                Application.ScreenUpdating = xUpdate
            End Sub

---

• Hit Alt+Q to return to Sheet.
• Save file as, Macro Enabled (.xlsm ).
• RUN the Macro.
• Select required cells from Col A.

---

• Finish with Ok.

---

• Select single cell for OUTPUT & finish with Ok.

---

• RUN the Macro again and repeat the procedure for Product column.
• You get final output.

N.B. This macro works for, one column at a time.