VBA - Excel：在不同的列中拆分字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16138589/
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
VBA - Excel : Split string in a different column
提问by Phalanx
I want the user of my excel file to enter a sentence in the cell "B2" and then have a Sub to parse the sentence in a different column (from D2 to Dn). So for example, if you type "aaa bbb ccc ddd" in B2, you should have as a result :
D2 : aaa
D3 : bbb
D4 : ccc
D5 : ddd
我希望我的 excel 文件的用户在单元格“B2”中输入一个句子，然后有一个 Sub 来解析不同列（从 D2 到 Dn）中的句子。因此，例如，如果您在 B2 中键入“aaa bbb ccc ddd”，则结果应该是：
D2 : aaa
D3 : bbb
D4 : ccc
D5 : ddd
I found how to split the sentence with VBA using the split function, but I have a hard time populating the column D as I don't know how to define the last row (Dn). Here is what I am using so far :
我找到了如何使用 split 函数用 VBA 拆分句子，但是我很难填充 D 列，因为我不知道如何定义最后一行 (Dn)。这是我目前使用的：
Sub splitAddress()
Dim strAddress As String
strAddress = Range("B2").Value
Range("D2:D9").Value = WorksheetFunction.Transpose(Split(strAddress, " "))
End Sub
I want to modify the "D2:D9" as D9 isn't always gonna be the last row of the column. How to write that it should populate from D2 to Dn according to the number of words in my B2 cell? Thanks in advance !
我想修改“D2:D9”，因为 D9 并不总是列的最后一行。如何根据我的 B2 单元格中的字数编写它应该从 D2 填充到 Dn？提前致谢 ！
回答by Glenn Stevens
There are probably more elegant ways to do this, but if you split the address into an array you can get the number of elements in the array using Uboundand use .Resizeto increase the number of rows in your range:
可能有更优雅的方式来做到这一点，但如果你的地址分成数组，你可以使用得到数组中元素的数量Ubound和使用.Resize，以增加你的范围内的行数：
Sub splitAddress()
Dim strAddress As String
Dim strAddressParts() As String
Dim numParts As Integer
strAddress = Range("B2").Value
strAddressParts = Split(strAddress, " ")
numParts = UBound(strAddressParts) + 1
Range("D2").Resize(numParts).Value = WorksheetFunction.Transpose(strAddressParts)
End Sub
回答by Simon1979
a loop like below would do it for you:
像下面这样的循环会为你做：
Sub splitAddress()
Dim i As Integer, x As Integer
Dim c As Range
i = 0
x = 1
Set c = Range("A5")
i = InStr(c.Value, " ")
c.Offset(0, x).Value = Left(c.Value, i - 1)
x = x + 1
i = InStr(i, c.Value, " ")
Do Until InStr(i + 1, c.Value, " ") = 0
c.Offset(0, x).Value = Mid(c.Value, i + 1, InStr(i + 1, c.Value, " ") - i)
i = InStr(i + 1, c.Value, " ")
x = x + 1
Loop
End Sub
