How to import multiple text files from a folder into one worksheet?
Suppose you have a scenario where you need to consolidate data for reporting or analysis, and you receive information as multiple text files, all saved in a single folder. Manually copying and pasting content from each text file into one worksheet can be extremely time-consuming and prone to errors, especially if the number of files is large or the files are updated regularly. Fortunately, there are efficient ways to automatically import and merge these text files into a single worksheet in Excel. This article presents practical methods to achieve this task, ensuring accuracy and saving considerable manual effort. These methods are suitable for use in many practical situations, such as consolidating survey results, importing log data, or combining fragmented text exports from various sources into a single dataset.
Below, you will find several approaches to quickly import multiple text files from a folder into Excel. Each method includes step-by-step guidance, practical tips, and notes to help you avoid common pitfalls.
Import multiple text files from one folder into a single sheet with VBA
Import text file to the active cell with Kutools for Excel 
Import multiple text files from one folder into a single sheet with VBA
One efficient way to import all text files from a specific folder into Excel is using a VBA macro. This solution is particularly suitable if you frequently perform this operation or need to automate repetitive tasks without additional add-ins. It allows you to quickly process a large number of files and minimizes manual intervention. However, using VBA requires enabling macros, which may be restricted in some environments, and basic familiarity with the Visual Basic for Applications editor.
Before starting, it is recommended to back up your workbook since macro operations cannot be easily undone. Ensure your text files are formatted consistently, as unpredictable structures might require manual cleanup after import.
1. Open the workbook where you want to import text files, and press Alt + F11 to open the Microsoft Visual Basic for Applications window.
2. In the VBA editor, click Insert > Module, then copy and paste the following VBA code into the module window:
VBA: Import multiple text files from one folder to one sheet
Sub Test()
'UpdatebyExtendoffice
Dim xWb As Workbook
Dim xToBook As Workbook
Dim xStrPath As String
Dim xFileDialog As FileDialog
Dim xFile As String
Dim xFiles As New Collection
Dim I As Long
Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
xFileDialog.AllowMultiSelect = False
xFileDialog.Title = "Select a folder [Kutools for Excel]"
If xFileDialog.Show = -1 Then
xStrPath = xFileDialog.SelectedItems(1)
End If
If xStrPath = "" Then Exit Sub
If Right(xStrPath, 1) <> "\" Then xStrPath = xStrPath & "\"
xFile = Dir(xStrPath & "*.txt")
If xFile = "" Then
MsgBox "No files found", vbInformation, "Kutools for Excel"
Exit Sub
End If
Do While xFile <> ""
xFiles.Add xFile, xFile
xFile = Dir()
Loop
Set xToBook = ThisWorkbook
If xFiles.Count > 0 Then
For I = 1 To xFiles.Count
Set xWb = Workbooks.Open(xStrPath & xFiles.Item(I))
xWb.Worksheets(1).Copy after:=xToBook.Sheets(xToBook.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = xWb.Name
On Error GoTo 0
xWb.Close False
Next
End If
End Sub3. Press F5 while the cursor is inside the code to execute. A dialog will prompt you to select the folder containing your target text files. See screenshot:
4. Click OK. The macro will import each text file as a new worksheet in your active workbook. Please note: if the files are large or numerous, this process may take some time. Check the result:
Tip: If your text files include headers or specific delimiters, you may need to edit the VBA code to suit your data structure. In case of errors, ensure your folder path and file types are correct, and that no text files are open elsewhere on your system. If files with identical names exist, worksheet naming conflicts might occur.
Import text file to the active cell with Kutools for Excel
For cases where you only need to insert the content of a single text file at a specific location within your worksheet, Kutools for Excel offers an intuitive solution through its Insert File at Cursor utility. This is particularly useful when merging selected data snippets or importing reference notes into defined cells or ranges. Unlike VBA solutions, Kutools requires no coding and provides a user-friendly interface for importing text files to any target cell.
After free installing Kutools for Excel, follow these steps:
1. Click to select the cell where you want the text file's content to be imported, then go to Kutools Plus > Import/ Export > Insert File at Cursor. See screenshot:
2. A dialog box appears. Click Browse to open the file selection dialog. In the file type drop-down, select Text Files and locate the text file you want to insert. See screenshot:
3. Click Open followed by OK. The contents of your chosen text file will be inserted into the selected cell. See screenshot:
Notes: The text content is imported into the selected cell, preserving the original formatting as much as possible. For best results, ensure that the target cell has enough space to display or expand the imported data. For multiple simultaneous imports, this feature needs to be used repeatedly or combined with Kutools' batch tools.
Summary: For simple or one-off imports of single files, Kutools offers user-friendly options directly in the worksheet, while VBA is best for users needing deeper customization or batch processing without relying on add-ins.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in