Parse Text file

I have coded this so far:

Sub FileName()
' Open file and place content into worksheet.

    Dim dlgOpen As FileDialog
    Dim fields As String, FileName As String, target As String
    Dim fileHandle As Integer
    Dim i As Long, j As Long


    Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
    With dlgOpen
        'we want just the one file
        .AllowMultiSelect = False
        .Show
        For i = 1 To .SelectedItems.Count
            FileName = .SelectedItems(i)
        Next
    End With
    
    'get counter ready
    j = 0
    
    'prepare the file handle
    fileHandle = FreeFile()

    Open FileName For Input As fileHandle
    Do Until EOF(fileHandle)
        Line Input #fileHandle, fields

        Range("A1").Offset(j, 0).Value = fields
        j = j + 1
           
    Loop

    Close #1

End Sub

Once the file is inputted into the excel sheet it all goes into column A. I want to use vba to separate each column the end results should have column A and B filled. Attach is the file I used to open and wanting to parse.

Hi Noodle,

Try adding this bit of code to the end of your macro (just before End Sub):

    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Space:=True, _
        FieldInfo:=Array(Array(1, 1), Array(2, 1))

Hope that helps!

Hello noodle48,

I made a few changes to your code. The fields variable is split using the space delimiter into an 1-D array. This array can then be directly loaded into the cells Ax:Bx in your loop. I have marked the additions in bold text,

Sub FileName()
' Open file and place content into worksheet.

    Dim dlgOpen As FileDialog
    Dim fields As String, FileName As String, target As String
    Dim fileHandle As Integer
    Dim i As Long, j As Long


    Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
    With dlgOpen
        'we want just the one file
        .AllowMultiSelect = False
        .Show
        For i = 1 To .SelectedItems.Count
            FileName = .SelectedItems(i)
        Next
    End With
    
    'get counter ready
    j = 0
    
    'prepare the file handle
    fileHandle = FreeFile()

    Open FileName For Input As fileHandle
    Do Until EOF(fileHandle)
        Line Input #fileHandle, fields

        Range("A1:B1").Offset(j, 0).Value = Split(fields, " ")
        j = j + 1
           
    Loop

    Close #1

End Sub