Copy values from text file to excel

Sivagokuk

In the discussions above, you confirmed that the text fields aren't always in the same sequence and won't be in same order.

This makes life tricky (you really should considered a structured format, e.g. comma-separation) but the attached file has your 6 headers and a new blue button labelled "Import header-matching values", linked to the macro below, It will import text files with contents like:

First name=David last-name=dav age=32 city=tx car=maruti mobile=nokia
First name=Siva last-name=Kumar age=29 city=   car=bmw z510

and - provided the text file has entries identical to the header (so "Last-name" not "Last name") - will handle fields in any order or missing in a line but is limited to importing a single word per field. You'd need to have "BMW_z510" to get both words.

It will import text into free rows after any existing entries in A:F

I've added comments so you can perhaps follow what is happening:

Sub ImportUnstructuredText()

    Dim ThisLine As Variant
    Dim fileFilterPattern As String
    Dim MyOperator As String, n As Long, p As Long
    Dim Fields() As Variant, FieldStart As Long, NextOp As Long
    Dim NxtFree As Long

    MyOperator = "="
    ' work out next unused row in any column
    NxtFree = ActiveSheet.UsedRange.Rows.Count + 1
    ' load headers to array
    Fields = ActiveSheet.Range("A1:F1").Value

    ' Open a file but just look at text files
    fileFilterPattern = "Text Files *.txt,*.txt"

    ThisLine = Application.GetOpenFilename(fileFilterPattern)

    If ThisLine = False Then
        MsgBox "No file selected."
        Exit Sub
    End If

    Application.ScreenUpdating = False

    Open ThisLine For Input As #1
    ' Look at each line of the file
    Do Until EOF(1)
        Line Input #1, ThisLine
        ' If the line contains something
        If Len(ThisLine) > 0 Then
            ' add a trailing space
            ThisLine = ThisLine & " "
            ' loop through headers and search for in text line
            For p = 1 To 6
                'see if field name is in line
                If InStr(LCase(ThisLine), LCase(Fields(1, p)) & MyOperator) > 0 Then
                    'determine where any text starts after column header and operator (compare lowercase)
                    FieldStart = InStr(LCase(ThisLine), LCase(Fields(1, p)) & MyOperator) + Len(Fields(1, p) & MyOperator)
                    ' calculate where next space is after that
                    NextOp = InStr(FieldStart, ThisLine, " ")
                    ' put text between under that header
                    Cells(NxtFree + n, p).Value = Mid(ThisLine, FieldStart, NextOp - FieldStart + 1)
                End If
            Next p
        End If
        ' Point to next row
        n = n + 1
    Loop

    ' Done so close the file for input
    Close #1

    Application.ScreenUpdating = True
    'tell user
    MsgBox "Imported " & n & " records from text file"

End Sub

Hope this helps- if so, please remember to mark it as Selected.