Copy previous lines from a specific string in a text file and paste them to Excel cell.

I have a text file, its look like...

word 1


word 2


word 3


I want to find all the strings "Line" in a text file, and copy the previous line (word 1, word 2, word 3) before "Line" and paste them into Excel cells. Please suggest.
Alex KrakovAsked:
Who is Participating?
Ejgil HedegaardCommented:
It could be something like this

Option Explicit

Sub GetText()
    Dim fName As String
    Dim Word1 As String, Word2 As String, i As Long
    'Some code to define the text file to use
    'fName is the full path to the text file
    'Something like
    fName = "C:\Test\FindLine.txt"
    'Open the text file for input
    Open fName For Input As #1
    'Read the text file
    Do Until EOF(1)
        Word1 = Word2
        Input #1, Word2
        If Word2 = "Line" Then
            'Some code to specify where to put the Word in the Excel sheet
            'Here listing in column A in active sheet
            i = i + 1
            Cells(i, 1) = Word1
        End If
    'Close the text file
    Close #1
End Sub

Open in new window

Alex KrakovAuthor Commented:
Thanks Ejgil Hedegaard. It works!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.