Text To Excel

hi Everyone

I basically have a large text file and I want to convert into Excel exactly as I have attached both text & excel files here.
Can anyone help me how can I convert/import this 'text' file into Excel
P.S.: Please note that text file data is too large this is just sample so I can't do 'tab' settings.
any other shorter way if anyone know would be highly appriciated

Thanks
Rocky

Hi Rocky
can you check the text file as the data has different layouts
Is the data grouped over four or five lines?

Hi Rocky
with out knowing the possible data layouts something like this

Sub ReadTxt()
    Dim tmpCode, lastObj As String
    Open "C:\ Your File Path \rocky text.txt" For Input As #1
    Do While Not EOF(1)
        Codes = ReadLines
        xRow = xRow + 1
        If Codes(0) <> "End_Of Line" Then
            xCol = 1
            For xcount = 1 To 5
                For Each xValue In Split(Codes(xcount), " ")
                              If xValue <> "" Then
                Cells(10 + xRow, xCol).Value = xValue
               xCol = xCol + 1
               End If  
  xCol = xCol + 1
               
               Next xValue
            Next xcount
        Else
            GoTo exitLoop
        End If
    Loop
exitLoop:
    Close #1
End Sub
Function ReadLines() As Variant
    On Error GoTo endfile
    Dim codeStr, valStr As String
    Line Input #1, LineOne
    Line Input #1, LineTwo
    Line Input #1, LineThree
    Line Input #1, LineFour
    Line Input #1, LineFive
    Line Input #1, LineSix
    ReadLines = Array(LineOne, LineTwo, LineThree, LineFour, LineFive, LineSix)
    Exit Function
endfile:
    ReadLines = Array("End_Of Line", "End_Of Line")
End Function

Hi rocky
This will just add the text file to the worksheet per 4 or five grouping but there are to many variables to work out what goes where
How do you create the text file? If you could add the delimiters to the text file it would be easier

Option Explicit
Sub ReadString()
    Dim sLine As String
    Dim sFName As String
    Dim iFNumber As Integer
    Dim lRow As Long
    Dim LColumn As Long
    Dim vValues
    Dim iCount As Integer
    sFName = "C:\"Your Path"\147 Entry.txt"
    iFNumber = FreeFile
    Open sFName For Input As #iFNumber
    lRow = 6
    LColumn = 0
    Do
        Line Input #iFNumber, sLine
        vValues = Split(sLine, " ")
        With Sheets("Sheet1")
            For iCount = LBound(vValues) To UBound(vValues)
                If vValues(iCount) = "," Then
                    lRow = lRow + 1
                    LColumn = 0
                Else
                    LColumn = LColumn + 1
                    .Cells(lRow, LColumn) = vValues(iCount)
                End If
            Next iCount
        End With
    Loop Until EOF(iFNumber)
    Close #iFNumber
End Sub

Hi Rocky
conversion from PDF is a problem as there is no real pattern to follow; Its near impossible to allow for all the variations in the layout.
The PDF is created from a word document?

Hi Rocky
veiwing the pdf won't help.. it just the way the data is layed-out.. to many variables to capture them all fro the table..
Can you control how the data is inputed?

Hi rocky2011
change the file path name to the txt files and this will take out another variable

Option Explicit
Sub ReadStringLines()
    Dim sLine As String
    Dim sFName As String
    Dim iFNumber As Integer
    Dim lRow As Long
    Dim LColumn As Long
    Dim vValues
    Dim iCount As Integer
    Dim mMarker As Boolean
    sFName = "C:\????????????????\147 Entry.txt"' Add Correct File Path
    iFNumber = FreeFile
    Open sFName For Input As #iFNumber
    lRow = 2
    LColumn = 0
    Do
        Line Input #iFNumber, sLine
        vValues = Split(sLine, " ")
        With Sheets("Sheet2")
            For iCount = LBound(vValues) To UBound(vValues)
               If iCount = 0 And Left(vValues(iCount), 6) = "assign" And LColumn = 0 Then
                 mMarker = True
               ElseIf iCount = 0 And vValues(iCount) = "Page" And LColumn = 0 Then
                 mMarker = False
               End If
                If vValues(iCount) = "," Then
                    lRow = lRow + 1
                    LColumn = 0
                ElseIf mMarker Then
                    LColumn = LColumn + 1
                    .Cells(lRow, LColumn) = vValues(iCount)
                End If
            Next iCount

        End With
    Loop Until EOF(iFNumber)
    Close #iFNumber
End Sub

Hi rocky2011,
147.txt has another different layout with added line breaks
since the txt file was made from a PDF I'm afraid it is not practical to convert it to the table you require

Hi Rocky;

I don't work with text files, but since you asked me to look at it I'll try.
This code DEPENDS on a "," being at the end of each line and a SPACE, or CR or LF to separate the text for each column.

I tried it on "new 147.txt" and it worked fine, but I don't see any reason why it would not work on any of the formats you have uploaded.

Sub ReadTextFile()
    Dim sLine As String
    Dim sLongLine As String
    Dim sFName As String
    Dim iFNumber As Integer
    Dim lRow As Long
    Dim iColumn As Integer
    Dim vValues
    Dim iCount As Integer

    sFName = "C:\????????????????\147 Entry.txt"        ' Add Correct File Path
    iFNumber = FreeFile
    Open sFName For Input As #iFNumber
    lRow = 2
    iColumn = 0
    Do
        sLongLine = ""
        Do While InStr(sLongLine, ",") = 0
            Line Input #iFNumber, sLine
            sLongLine = sLongLine & " " & sLine
            'Debug.Print sLine
            'Debug.Print sLongLine
            If EOF(iFNumber) Then
                Exit Do
            End If
        Loop
        vValues = Split(sLongLine, " ")
        If IsArray(vValues) Then
            With Sheets("Sheet2")
                For iCount = LBound(vValues) To UBound(vValues)
                    If vValues(iCount) = "," Then
                        lRow = lRow + 1
                        iColumn = 0
                    ElseIf Trim(vValues(iCount)) = "" Then
                        'skip this value
                    Else
                        iColumn = iColumn + 1
                        .Cells(lRow, iColumn) = vValues(iCount)
                    End If
                Next iCount
            End With
        End If        'IsArray(vValues)
    Loop Until EOF(iFNumber)
    Close #iFNumber
End Sub