How to Extract Quotes From a Text File Using VBA in Excel

Struggling to pull quotes from a text file? Need to analyze dialogue from a script or gather research excerpts? In this step-by-step guide, we will show you how to extract text between quotes from a text file using VBA (Visual Basic for Applications) in Microsoft Excel. No need for manual copying and pasting anymore – work smarter by using VBA.

For those of you who prefer to watch a video to see how this looks like in action, you can check out the above video. Now, let’s go through the code:

Getting Started

Before we begin, you will need:

• A Text File: The code extracts quotes from a text file (*.txt). If you don’t already have one, prepare this file using a text editor like notepad.
• Microsoft Excel: You’ll need Microsoft Excel either installed on your computer or on Office 365 to run the VBA script.

Step 1: Setting Up Your VBA Project in Excel

1. Open a New Workbook: Launch Microsoft Excel and create a new, blank workbook.
2. Access the VBA Editor: Press the keyboard shortcut Alt + F11 to open the VBA Editor.
3. Insert a Module: In the VBA Editor, navigate to Insert > Module to create a new module where you’ll write your code.

Step 2: The VBA Code for Extracting Quotes

Now, copy and paste the following VBA code into the module you just created:

Sub ImportQuotes()
    Dim filePath As String
    Dim fileNumber As Long
    Dim textLine As String
    Dim quotesDict As Object
    Dim quoteStart As Long
    Dim quoteEnd As Long
    
    filePath = "C:\Demos\Shakespeare.txt"
    Set quotesDict = CreateObject("Scripting.Dictionary")
    fileNumber = FreeFile
    Open filePath For Input As #fileNumber
    
    Do While Not EOF(fileNumber)
        Line Input #fileNumber, textLine
        quoteStart = 0
        Do
            quoteStart = InStr(quoteStart + 1, textLine, Chr(34))
            If quoteStart > 0 Then
                quoteEnd = InStr(quoteStart + 1, textLine, Chr(34))
                If quoteEnd > 0 Then
                    Dim quote As String
                    quote = Mid(textLine, quoteStart + 1, quoteEnd - quoteStart - 1)
                    If Not quotesDict.Exists(quote) Then
                        quotesDict.Add quote, quote
                    End If
                    quoteStart = quoteEnd
                Else
                    quoteStart = Len(textLine) + 1
                End If
            Else
                Exit Do
            End If
        Loop While quoteStart <= Len(textLine)
    Loop
    
    Close #fileNumber
    
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.Cells.ClearContents
    
    Dim i As Long
    For i = 0 To quotesDict.Count - 1
        ws.Cells(i + 1, 1).Value = quotesDict.Keys()(i)
    Next i
    MsgBox "Quotes imported successfuly!", vbInformation
        
End Sub

Step 3: Customising the Code for Your Needs

• Update the File Path: Replace “C:\Demos\shakespeare.txt” with the actual file path of your text file.
• Change the Output Destination: To send the extracted quotes to a different column in your Excel worksheet, adjust the ws.Cells(i + 1, 1).Value = quotesDict.Keys(i) line. For instance, modify the column number to 2 to place the quotes in column B.

Step 4: Running the VBA Code

1. Save Your Workbook: Save your workbook as a macro enabled Excel Workbook before running the code.
2. Execute the Code: In the VBA Editor, press F5, or go to Run > Run Sub/UserForm to run your VBA code.

That’s it! Your code will now scan your text file line by line, find text enclosed in quotation marks, and insert each unique quote on a new row in the active sheet of your Excel workbook.

How the Code Works

The code can be broken down into the following components:

• Declarations: This section at the top sets up and assigns variables to hold the file path, file number, text lines, the quote dictionary, and positions of quote markers.
• File Handling: The code opens your specified text file, ready to read its contents line by line. Once done, we will also close the text file.
• Finding Quotes: The code loops through all the lines and searches each line for the opening quote character (“). If found, it then locates the corresponding closing quote.
• Extracting and Storing Quotes: The text between the opening and closing quotes is extracted. To avoid duplicates, the extracted quote is added to a dictionary object. Dictionaries in VBA can only hold unique values.
• Outputting to Excel: Once all quotes are collected, the code will transfer them to your active Excel worksheet, placing each quote on a new row.
• User Feedback: When the process is completed successfully, a message box will pop up to confirm the completion.

Troubleshooting Tips

• Error Messages: If you encounter errors, review the error message for clues. Common errors might involve incorrect file paths or file permissions. VBA’s debugger can help you step through the code to pinpoint the problem.
• Missing Quotes: Make sure your quotes in the text file are correctly enclosed in double quotation marks (“). The code searches for Chr(34) to identify quotes. If you use single quotation marks, you will need to modify your code to search for Chr(39) instead.
• Unexpected Output: If the results aren’t as expected, you will need to examine the logic of your code. One common source of errors are the conditions and loops. You might need to adjust them for things like multi-line quotes.

Conclusion: Efficiently Extract Quotes with VBA

This VBA script is a powerful tool for extracting quotes from text files and importing them into Excel, simplifying text analysis, research, and other data-handling tasks. If you’re keen to learn more about how you can improve your productivity with VBA, you might want to check out our post on VBA in PowerPoint.