How to extract data from another workbooks or text files in Excel?
Normally we can copy data from an opening workbook and then paste into another workbook. However, sometimes you may want to extract data from a closed workbook without opening it, or even extract data from other file types, such as text files, how to get it done in Excel? In this article I will show you a way to extract data from another closed workbooks or text files easily in Excel.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Kutools for Excel is an excellent Excel add-in with dozens of handy utilities. It’s Insert File at Cursor utility can help you to extract data from closed workbook, text files or any other files and insert into current worksheet easily.
Step 1: Put cursor at the cell where you will insert extracted data. In our example, we select the Cell D4. Then click Enterprise > Import / Export > Insert File at Cursor.
Step 2: In the opening Insert File at Cursor dialog box, click the Browse button.
Note: If you want to extract data from other files without formulas, please check the Values only (no formulas) option; if you want to extract data without formatting, please check the Do not transfer formatting option.
Step 3: In the coming Select a file to be inserted at the cell cursor position dialog box, open the folder that the workbook or other file is in, click to select the file you will extract, and click the Open button.
1. For inserting data from Text file, please check the Text Files is selected behind the File name box in the Select a file to be inserted at the cell cursor position dialog box.
2. If you select a workbook with multiple worksheets, a Select a Worksheet dialog box will be thrown out. Just click to select the worksheet you will extract data from, and click the Ok button.
Now you will see all data in the specified worksheet is extracted and inserted into current worksheet.
With Kutools for Excel's Combine worksheets and and workbooks feature, you can easily extract data from multiple worksheets and workbooks and place extracted data into one sheet. Please do as follows:
1. Click Enterprise > Combine.
2. In the opening Combine Worksheets - Step 1 of 3 dialog box, please check the Combine multiple worksheets from workbooks into one worksheet option, and click the Next button.
3. In the Combine Worksheets - Step 2 of 3 dialog box, please:
(1) Click Add > File / Folder to add workbooks you will extract data from;
(2) In the Workbook list section, please check the workbooks you will extract data from;
(3) In the Worksheet list section, please check the worksheets you will extract data from;
(4) In the worksheet list section, click the browse button behind each worksheet name to specify the range you will extract from each worksheet.
(5) Click the Next button.
4. In the Combine Worksheets - Step 3 of 3 dialog box, please specify the combination rule as you need, and click the Finish button.
Now all specified range from closed workbooks are extracted and placed in one worksheet.