在 excel (vba) 中拆分字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8231368/
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
Split strings in excel (vba)
提问by captainrad
I am currently using this code(from a fellow user here) to find every cell in column b1 and to find the ones that contain a ";" something like "hello;goodbye". The code will split the cell at the ";" and place "goodbye" directly beneath "hello;" on an entirely new row..
我目前正在使用此代码（来自此处的其他用户）来查找 b1 列中的每个单元格并查找包含“;”的单元格 诸如“你好；再见”之类的东西。代码将在“;”处拆分单元格 并将“再见”直接放在“你好”之下；在一个全新的行..
What I need now is this... if a cell contains multiple";" (ie "hello;goodbye;yo;hi;hey") it will split at EACH ";" not just the first and then move each to a new row directly beneath the other...
我现在需要的是......如果一个单元格包含多个“;” （即“你好；再见；哟；嗨；嘿”）它会在每个“;” 不只是第一个，然后将每个移动到另一个正下方的新行......
What changes do I need to make?
我需要做哪些改变？
Dim r1 As Range, r2 As Range
Dim saItem() As String
For Each r1 In ActiveSheet.Range("B1", Cells(Application.Rows.Count, 2).End(xlUp))
If InStr(1, r1.Value2, ";") > 0 Then
saItem = Split(r1.Value2, ";")
r1 = Trim$(saItem(0)) & ";"
r1.Offset(1).EntireRow.Insert (xlDown)
r1.Offset(1) = Trim$(saItem(1))
End If
Next r1
回答by aevanko
I know it's close to what you have, but I wanted to suggest you use Application.ScreenUpdating. This will save considerable time, especially when inserting/deleting rows in Excel. I also wanted to suggest you change the variable names to somehting a little more meaningful.
我知道它与您所拥有的很接近，但我想建议您使用 Application.ScreenUpdating。这将节省大量时间，尤其是在 Excel 中插入/删除行时。我还想建议您将变量名称更改为更有意义的名称。
Sub SplitCells()
Application.ScreenUpdating = False
Dim strings() As String
Dim i As Long
For i = Cells(Rows.Count, 2).End(xlUp).Row To 1 Step -1
If InStr(Cells(i, 2).Value, ";") <> 0 Then
strings = Split(Cells(i, 2).Value, ";")
Rows(i + 1 & ":" & i + UBound(strings)).Insert
Cells(i, 2).Resize(UBound(strings) + 1).Value = _
WorksheetFunction.Transpose(strings)
End If
Next
Application.ScreenUpdating = True
End Sub
P.S. Smaller alterations is to use "2" instad of "B". If you are using cells() instead of Range(), may as well go all the way :)
PS 较小的改动是使用“2”代替“B”。如果您使用 cell() 而不是 Range()，不妨一路走下去:)
回答by captainrad
I found an answer over at
我找到了答案
http://www.excelforum.com/excel-programming/802602-vba-macro-to-split-cells-at-every.html
http://www.excelforum.com/excel-programming/802602-vba-macro-to-split-cells-at-every.html
This is the solution I was given:
这是我得到的解决方案：
Sub tgr()
Dim rindex As Long
Dim saItem() As String
For rindex = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
If InStr(Cells(rindex, "B").Value, ";") > 0 Then
saItem = Split(Cells(rindex, "B").Value, ";")
Rows(rindex + 1 & ":" & rindex + UBound(saItem)).Insert
Cells(rindex, "B").Resize(UBound(saItem) + 1).Value = WorksheetFunction.Transpose(saItem)
End If
Next rindex
End Sub
