How to extract data from another workbooks or text files in Excel?

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 Kutools Plus > 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.

Notes:

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.

Extract without formula and formatting:

Extract with formula and formatting:

Demo: Extract data from one closed workbook or text file in Excel

Extract data from multiple closed workbooks in Excel