vba Excel，拆分逗号分隔值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27598728/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel, split up comma separated values
提问by Martin-bt
I have an Excel sheet with 2 columns.
我有一个包含 2 列的 Excel 工作表。
Column1 contains values like:
Column1 包含如下值：
10969
11284
1222
1227
12669
12670
12671
12672
10969
11284
1222
1227
12669
12670
12671
12672
Column2 contains comma separated values like this:
Column2 包含逗号分隔的值，如下所示：
601010,6010,40,4010
601010,6010,40,4010
40,4010,6010,601010
40,4010,6010,601010
40,4010,6010,601010
40,4010,6010,601010
40,4010,6010,601010
40,4010,6010,601010
601010,6010,40,4010
601010,6010,40,4010
40,4010,6010,601010
40,4010,6010,601010
40,4010,6010,601010
40,4010,6010,601010
40,4010,6010,601010
40 ,4010,6010,601010
I need this split up differently, so instead of having column2 commaseparated, I need 1 row for each value i column 2 Column1 should use the same value for each string, so you get 4 rows with the same number in column1 and different numbers in column2
我需要以不同的方式拆分，因此不是将 column2 逗号分隔，而是每个值需要 1 行第 2 列 Column1 应为每个字符串使用相同的值，因此您会得到 4 行，列 1 中的数字相同，而列 2 中的数字不同
An example using the data from above
使用上述数据的示例
Column1
第一列
10969
10969
10969
10969
11284
11284
11284
11284
10969
10969
10969
10969
11284
11284
11284
11284
Column2
第 2 列
601010
6010
40
4010
601010
6010
40
4010
601010
6010
40
4010
601010
6010
40
4010
It is not the same values everytime, and there is more than 1000 lines, so its a big task to do it manually. As far as I can see this is not possible with "standard Excel", But maybe with VBA?
每次都不一样的值，而且有1000多行，所以手动完成是一项艰巨的任务。据我所知，使用“标准 Excel”是不可能的，但也许使用 VBA？
I hope someone can point me in the right direction.
我希望有人能指出我正确的方向。
Regards Martin
问候马丁
采纳答案by Petay87
As answered HERE
正如这里的回答
I believe the best solution for you would be to run the following VBA while highlighting/Selecting column B:
我相信最适合您的解决方案是在突出显示/选择 B 列的同时运行以下 VBA：
Sub columnsplit()
Dim c As Range
Dim splitv() As String
For Each c In Selection
splitv = Split(c.Value, ",")
If UBound(splitv) > 0 Then
c.EntireRow.Insert
c.Offset(-1, 0).Value = splitv(1)
c.Offset(-1, -1).Value = c.Offset(0, -1).Value
c.Value = splitv(0)
End If
Next c
End Sub
This should give you desired result.
这应该会给你想要的结果。
However, when I tested the code, it was sometimes not clear if the code had ran as you need to remember that Excel has a nice feature of adding commas for you to separate numerical values.
但是，当我测试代码时，有时并不清楚代码是否已运行，因为您需要记住 Excel 有一个很好的功能，可以为您添加逗号来分隔数值。
For example, where you might have 123456,123456789 this code will split it but Excel might then display 123,456 and 123,456,789 as the results. You will need to amend your formatting so that this does not happen.
例如，如果您可能有 123456,123456789，此代码将拆分它，但 Excel 可能会显示 123,456 和 123,456,789 作为结果。您需要修改格式，以免发生这种情况。
回答by teylyn
This is a perfect fit for Power Query. Suck the table into Power Query, split Column2 with the comma delimiter, select all but the first columns and click Unpivot.
这非常适合 Power Query。将表放入 Power Query，使用逗号分隔符拆分 Column2，选择除第一列之外的所有列，然后单击 Unpivot。
One of the major processing problems is that numeric data is separated with a comma, and a comma can also be a "thousand" separator in large numbers. I've tried to copy your data sample, but some rows come back as text with only numbers and commas, and other rows come back as very, very big numbers in exponential format.
一个主要的处理问题是数字数据用逗号分隔，在大数字中逗号也可以是“千”分隔符。我已尝试复制您的数据样本，但有些行以仅包含数字和逗号的文本形式返回，而其他行以指数格式以非常非常大的数字形式返回。
So you may need to do some work on defining the incoming data type. This may be an issue with regional settings. My regional settings have the comma as the thousand delimiter.
因此，您可能需要在定义传入数据类型方面做一些工作。这可能是区域设置的问题。我的区域设置以逗号作为千位分隔符。
You may be better off posting an actual Excel file with a file sharing service and post a link here.
您最好使用文件共享服务发布实际的 Excel 文件并在此处发布链接。
Power Query can do this -- that's a given. We just need to straighten out the data format to work out HOW to do it.
Power Query 可以做到这一点 - 这是给定的。我们只需要理顺数据格式就可以弄清楚如何去做。
