CODEWITHSUNDEEP
excel
haunted85
haunted85

Reputation: 1671

How to split one column of data into multiple columns in Excel

I am currently working in Microsoft Excel 2013 and I have one big column of data that I want to split into exactly three columns. I would like to go from this:

name_1
timestamp_1
comment_1
name_2
timestamp_2
comment_2
...
name_N
timestamp_N
comment_N

to this:

name_1 timestamp_1 comment_1
name_2 timestamp_2 comment_2
...
name_N timestamp_N comment_N

is there a way to acheive that?

Upvotes: 0

Views: 3333

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

And another way with formulas

Assume data is in A2:An

D2:  =INDEX($A$2:$A$1000,(ROWS($1:1)-1)*3+COLUMNS($A:A))

Fill right to F2. Then select D2:F2 and fill down as far as needed.

enter image description here

Upvotes: 0

Eric K.
Eric K.

Reputation: 834

Another non VBA way, add a column with numbering like below

1 name_1
2 timestamp_1
3 comment_1
1 name_2
2 timestamp_2
3 comment_2
...
1 name_N
2 timestamp_N
3comment_N

Select C2:E2, then type TRANSPOSE(B2:B4) Then CTRL + SHIFT + Enter

Then Duplicate the formula to record Num = 1 (Using autofilter)

enter image description here

Upvotes: 2

Eric K.
Eric K.

Reputation: 834

I presume that your data in single column. you can create a sub to done this

Sub rowToColumn()

nRow = 2 'Assume u have header on Row 1
Do until cells(nRow,"A") = ""
    cells(nRow, 2) = cells(nRow + 1, "A")
    cells(nRow, 3) = cells(nRow + 2, "A")
    'Then remove the additional row.....
    bla bla bla......

nRow = nRow + 1
Loop

End sub

Upvotes: 1

Related Questions