拆分具有可变数量空格的字符串 VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2168194/
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
Splitting a string with variable number of spaces VBA
提问by GVBA
I have a file with a bunch of numbers in columns. These numbers are separated by a variable number of spaces. I want to skip the first line and get all the other lines and separate each number on the line. Finally, I want to write each number in Excel. I've been able to get the lines and write them on Excel but I can't separate each number (I'm getting the whole line as one string).
我有一个列中有一堆数字的文件。这些数字由可变数量的空格分隔。我想跳过第一行并获取所有其他行并将行上的每个数字分开。最后，我想在 Excel 中写下每个数字。我已经能够获取这些行并将它们写在 Excel 上，但我无法将每个数字分开（我将整行作为一个字符串）。
Does any body know how to split a string that has a variable number of spaces?
有没有人知道如何拆分具有可变数量空格的字符串？
Here is my code.
这是我的代码。
Sub Test()
r = 0
With New Scripting.FileSystemObject
With .OpenTextFile("C:\Users\User\Desktop\File.tab", ForReading)
If Not .AtEndOfStream Then .SkipLine
Do Until .AtEndOfStream
ActiveCell.Offset(r, 0) = Split(.ReadLine, vbCrLf)
r = r + 1
Loop
End With
End With
End Sub
回答by Steve Homer
If you use the Excel worksheet function trim in place of the VBA function then excel will remove multiple spaces within a cell (not just from the left and right ends). Something like the below should solve the problem. I'm afraid I've not tested it as I haven't a copy of Excel handy.
如果您使用 Excel 工作表函数 trim 代替 VBA 函数，则 excel 将删除单元格内的多个空格（不仅仅是从左右两端）。像下面这样的东西应该可以解决问题。恐怕我没有测试过它，因为我手边没有 Excel 的副本。
Sub Test()
Dim splitValues As Variant
Dim i As Long
r = 0
With New Scripting.FileSystemObject
With .OpenTextFile("C:\Users\User\Desktop\File.tab", ForReading)
If Not .AtEndOfStream Then .SkipLine
Do Until .AtEndOfStream
ActiveCell.Offset(r, 0) = Split(.ReadLine, vbCrLf)
Application.Trim(ActiveCell.Offset(r, 0))
splitValues = Split(ActiveCell.Offset(r, 0), " ")
For i = 0 To UBound(x)
ActiveCell.Offset(r, i+1) = splitValues(i)
Next
Loop
r = r + 1
End With
End With
