拆分具有可变数量空格的字符串 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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 11:16:41  来源:igfitidea点击:

Splitting a string with variable number of spaces VBA

excelvbaexcel-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