[Excel VBA] How to Extract Lines Containing Specific Text from a Text File

2025年12月29日

When dealing with large log files or CSV files, you often need to extract only the lines that contain specific keywords. Searching manually takes time, but by using VBA, you can automatically extract and paste only the lines that match your conditions.

In this article, I will introduce a VBA macro that uses the ADODB.Stream object to read lines containing a specific string from a Shift-JIS text file.

Scenario

Target: A text file named logdata.csv saved in Shift-JIS encoding.
Content: Various messages are recorded on each line.
Goal: Extract only the lines containing the keyword “ERROR” and paste them into Excel.

VBA Code: Extracting Lines with Specific Text

Sub ExtractMatchingLines()
    Dim oneLine As String
    Dim currentCell As Range
    
    ' Set the starting cell for output
    Set currentCell = ThisWorkbook.Worksheets(1).Range("B2") 
    
    With CreateObject("ADODB.Stream")
        .Charset = "Shift-JIS" ' Specify character encoding
        .Type = 2 ' Text mode
        .LineSeparator = -1 ' CRLF (Windows format)
        .Open
        .LoadFromFile ThisWorkbook.Path & "\logdata.csv"
        
        ' Loop until the end of the file
        Do While Not .EOS
            oneLine = .ReadText(-2) ' Read one line at a time
            
            ' Check if the line contains "ERROR"
            If oneLine Like "*ERROR*" Then
                currentCell.Value = oneLine
                Set currentCell = currentCell.Offset(1) ' Move to the next row
            End If
        Loop
        
        .Close
    End With
End Sub

Process Flow and Key Points

Process	Explanation
`.LoadFromFile`	Loads the text file.
`.ReadText(-2)`	Reads the file one line at a time (The `-2` argument specifies reading by line).
*`Like "ERROR"`*	Checks if the line contains the string “ERROR”.
`.Offset(1)`	Moves the output position down by one row to output found lines consecutively.

Output Image

Lines output to Column B:

2025/07/01,ERROR,Connection Failed
2025/07/01,ERROR,Timeout

Only the lines containing “ERROR” are extracted and pasted neatly.

Advanced Tips

Exact Match: If you need an exact match instead of a partial match, use If oneLine = "String" instead of Like.
Japanese Keywords: You can also use Japanese keywords, such as Like "*エラー*".
Complex Patterns: If you need more complex matching, you can use VBScript.RegExp for regular expressions.

Important Notes

Character Encoding: The code specifies Shift-JIS. If your file is UTF-8, change .Charset to "UTF-8".
Line Separators: The LineSeparator = -1 (CRLF) setting is standard for Windows. For UNIX-based files, change it to 10 (LF).