How to split multiple rows into multiple columns in excel?

Question

So I have an excel file filled with 10,000 rows of data that is only on one column. The problem is it's not in the correct format. The columns have a pattern of:

"name"
"address"
"city"
"state"
"name"
"address"
"city"
"state"
"name"
...

but the problem is I need it to be "name" "address" "city" "state". I was wondering if there is a way to do this in Excel or maybe using a script. Any help would be appreciated. Thank you.

How are the values stored? Is there a delimiter? Provide more information and clarify what you need (perhaps provide a sample of what it should look like when done). — Dan, Nov 01 '13 at 21:18

score 2 · Answer 1 · edited May 23 '17 at 11:52

If your getting the data from sql... Change your query from vertical to horizontal using pivot. Now you don't have to worry about converting it in excel. See this post

@Laurence has a good formula based solution, however,if you need vba because your doing this over and over with new data dumps from sql here is a VBA solution.

Started with this:

enter image description here

Used this Macro:

Sub TransposeColA()
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    cntr = 1

    For rowNum = 1 To LastRow
        If Cells(rowNum, 1).Value <> "" Then
            Set rngA = Range(Cells(rowNum, 1), Cells(rowNum, 1).Offset(3, 0).Address)
            Range("B" & rowNum & ":E" & rowNum).Value = Application.Transpose(rngA)
            rngA.Delete
        Else
            Rows(rowNum).Delete Shift:=xlUp
            rowNum = rowNum - 1
            cntr = cntr + 1
            If cntr = LastRow Then Exit Sub
        End If
    Next rowNum
End Sub

And ended up with this:

enter image description here

score 1 · Answer 2 · answered Nov 01 '13 at 21:35

Here's a way to manually cross tab data in Excel:

Assume the data is in column A. Enter the following forumlae:

B1: =INT((ROW() - 1 ) * 4)
C1: =INDIRECT("A" & ($B1 + COLUMN() - COLUMN($B1)))

Now select C1 and drag the bottom right corner handle right four cells. You should see the first row of your data displayed

Finally select B1 to F1. Drag the bottom right corner handle down. The further down, the more rows you'll get.

How to split multiple rows into multiple columns in excel?

2 Answers2