Excel – Splitting a string with variable number of spaces VBA

excelvba

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).

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

Best Answer

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.


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

Related Solutions

Excel – Search a string from text file & Return the Line Number using VBA

I used a spin off of Me How's code example to go through a list of ~10,000 files searching for a string. Plus, since my html files have the potential to contain the string on several lines, and I wanted a staggered output, I changed it up a bit and added the cell insertion piece. I'm just learning, but this did exactly what I needed and I hope it can help others.

Public Sub ReadTxtFile()

    Dim start As Date
    start = Now

    Dim oFSO As Object
    Set oFSO = CreateObject("Scripting.FileSystemObject")

    Dim oFS As Object

    Dim filePath As String

    Dim a, b, c, d, e As Integer
    a = 2
    b = 2
    c = 3
    d = 2
    e = 1

    Dim arr() As String

    Do While Cells(d, e) <> vbNullString

            filePath = Cells(d, e)

            ReDim arr(5000) As String
            Dim i As Long
            i = 0

            If oFSO.FileExists(filePath) Then

                On Error GoTo Err

                Set oFS = oFSO.OpenTextFile(filePath)
                Do While Not oFS.AtEndOfStream
                    arr(i) = oFS.ReadLine
                    i = i + 1
                Loop
                oFS.Close
            Else
                MsgBox "The file path is invalid.", vbCritical, vbNullString
                Exit Sub
            End If

            For i = LBound(arr) To UBound(arr)
                If InStr(1, arr(i), "Clipboard", vbTextCompare) Then
                    Debug.Print i + 1, arr(i)
                    Cells(a + 1, b - 1).Select
                    Selection.Insert Shift:=xlDown
                    Cells(a, b).Value = i + 1
                    Cells(a, c).Value = arr(i)
                    a = a + 1
                    d = d + 1
                End If
            Next
            a = a + 1
            d = d + 1
    Loop

    Debug.Print DateDiff("s", start, Now)

    Exit Sub

Err:
    MsgBox "Error while reading the file.", vbCritical, vbNullString
    oFS.Close
    Exit Sub

End Sub

Best Answer

Related Solutions

Excel – Search a string from text file & Return the Line Number using VBA

Related Topic