如何在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:

我目前正在使用Microsoft Excel 2013,我有一大列数据,我想分成三列。我想离开这个:

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?

有没有办法实现这一点?

3 个解决方案

#1

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

#2

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

另一种非VBA方式,添加如下编号的列

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

选择C2:E2,然后键入TRANSPOSE(B2:B4)然后按CTRL + SHIFT + Enter

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

然后复制公式以记录Num = 1(使用自动过滤器)

#3

And another way with formulas

和公式的另一种方式

Assume data is in A2:An

假设数据在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.

向右填充F2。然后选择D2:F2并根据需要填写。

#1