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

enter image description here


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?

  • This might be solved at import; The original data appears to be separated by a tab or space or sim, and that is the same as columns in Excel. Can you modify the import or is that something set up by someone else? – Ack Mar 30 '20 at 16:51
  • 1
    As @Ack implies, can you provide a sample of the original CSV file? Preferable as text within your question. It might be easier to modify at that point. – Ron Rosenfeld Mar 30 '20 at 17:04
  • Hi @faisalv,, this needs VBA (Macro), if u are comfortable with it then please edit your post and specified that Macro will also work for you as well as add new TAG VBA to the question. – Rajesh S Mar 31 '20 at 7:17
  • Unfortunately I cannot modify the import. – faisalv Apr 8 '20 at 11:09
  • @faisalv,, check my post for what you are looking for. ☺ – Rajesh S Apr 9 '20 at 7:28

VBA Macro solves the issue:

enter image description here

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
                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.

enter image description here

  • Finish with Ok.

enter image description here

  • Select single cell for OUTPUT & finish with Ok.

enter image description here

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

enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.