Converting a text file to an excel spread sheet

This is expanding on a question I had earlier but a little more complicated…..

I need to import a text file into excel and do the following: (SEE ATTACHED FILE)

Convert data in rows to columns deleting out information I do not need.
Data needed is Information on Object #, Name, Coordinates (X,Y,Z), Segment length, Number of Sheets welded, weld type.

Not all Objects have Coordinates and not all Objects have segment length.

Try this:

Option Explicit

Sub ImportObjectList()
'Author:    Jerry Beaucaire
'Date:      9/16/2010
'Summary:   Import a text file and extract/format specific data
Dim fName As String
Dim LR As Long, FR As Long, NR As Long, Rw As Long
Dim MyARR As Variant, Vals As Variant

Worksheets.Add

'Select a file
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        .Filters.Add "All Files", "*.*"        'everything
        .Filters.Add "Text Files", "*.txt", 1  'default
        .Show
        If .SelectedItems.Count > 0 Then
            fName = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
    
Application.ScreenUpdating = False
'Import the file
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, _
        Destination:=Range("A1"))
        .Name = "ame--"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
'Create new table
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("B1:I1").Value = [{"OBJECT #","NAME","Coord X","Coord Y","Coord Z","SEG LGTH","WELDS","TYPE"}]
'    Range("D:F").NumberFormat = "0.00000000000"
    FR = Cells.Find("===", LookIn:=xlValues, LookAt:=xlPart).Row + 1
    MyARR = Application.WorksheetFunction.Transpose(Range("A" & FR & ":A" & LR).Value2)
    NR = 1
    For Rw = LBound(MyARR) To UBound(MyARR)
        If Left(MyARR(Rw), 21) = "Information on object" Then
            NR = NR + 1
            Vals = Split(Trim(MyARR(Rw)), " ")
            Range("B" & NR) = Vals(UBound(Vals))
        ElseIf Left(MyARR(Rw), 4) = "Name" Then
            Vals = Split(Trim(MyARR(Rw)), " ")
            Range("C" & NR) = Vals(UBound(Vals))
        ElseIf Left(MyARR(Rw), 11) = "Coordinates" Then
            Vals = Split(Trim(MyARR(Rw)), " ")
            Range("D" & NR) = Vals(UBound(Vals))
            Vals = Split(Trim(MyARR(Rw + 1)), " ")
            Range("E" & NR) = Vals(UBound(Vals))
            Vals = Split(Trim(MyARR(Rw + 2)), " ")
            Range("F" & NR) = Vals(UBound(Vals))
            Rw = Rw + 2
        ElseIf Left(MyARR(Rw), 14) = "SEGMENT LENGTH" Then
            Range("G" & NR) = Replace(Replace(Trim(MyARR(Rw)), " (string)", ""), "SEGMENT LENGTH = ", "")
        ElseIf Left(MyARR(Rw), 23) = "NUMBER OF SHEETS WELDED" Then
            Vals = Split(Trim(MyARR(Rw)), " ")
            Range("H" & NR) = Vals(UBound(Vals) - 1)
        ElseIf Left(MyARR(Rw), 9) = "WELD_TYPE" Then
            Range("I" & NR) = Replace(Replace(Trim(MyARR(Rw)), " (string)", ""), "WELD_TYPE  = ", "")
    End If
Next Rw
    
    Columns("A:A").Delete xlShiftToLeft
    Columns("A:H").EntireColumn.AutoFit
    Range("A1:H1").Font.Bold = True
    Range("A2").Select
    ActiveWindow.FreezePanes = True

Application.ScreenUpdating = True
End Sub

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.