THE DATA CLEANING PACK

Get the Word-to-Excel practice files

The messy Word table from the video, plus everything else I use to clean up imported data. Free, instant, yours to keep.

What’s in the pack

The Word document and finished Excel sheet from this video
A deliberately messy dataset to practise on
Merge, append and unpivot templates
My 15 most-used Power Query snippets (PDF)
Works in Excel 365, 2021 and 2019 — PC and Mac

Send it to my inbox

Free, instant, and yours to keep.

No spam, ever. Unsubscribe with one click.
One practical Excel tip a week.

How to Convert a Word Document into Excel

Watch the 4-minute video or follow the written steps below. The practice files above contain the same Word table, so you can follow along.

Step-by-step

The example is a PayPal statement saved as a Word document. Going through a plain-text file is what lets Excel split the text into proper columns, and you can do it without opening Power Query.

1. Save the Word document as plain text

In Word, go to File > Save As > Browse, choose a folder and set Save as type to Plain Text (*.txt). Click Save, keep the default settings in the File Conversion window and click OK. Then close Word.

2. Restore the Text Import Wizard (Excel 365)

Microsoft replaced the classic Text Import Wizard with the Get & Transform tools. They’re powerful but complicated for a simple import. The good news is that the old wizard is still there, just well hidden. Go to File > Options > Data, and in the lower section tick From Text (Legacy). You can also restore the other legacy wizards there (Access, web, SQL Server) if you use them. Click OK.

3. Start the import

Open a new workbook, go to Data > Get Data > Legacy Wizards > From Text (Legacy). Select the text file you saved and click Import. (In older versions of Excel, this is simply Data > From Text.)

4. Choose Delimited

In the wizard, choose Delimited. If the first row of your file holds column headers, tick My data has headers. If it doesn’t, leave it unticked.

5. Check the columns

Click Next. The preview shows the data separated into columns. The PayPal statement uses tabs, so Tab is the delimiter to tick.

6. Set formats and finish

Optionally set a data format for each column, then click Finish, keep cell A1 as the destination and click OK. The Word data is now in cells, ready for totals and other calculations.

YOUR NEXT STEP

You've got the files. Here's how to stop doing this by hand.

The method in this video is perfect for a one-off import. But if you’re cleaning the same report every week — the same stray commas, the same merged cells, the same half-hour — there’s a better answer. Power Query does the whole job once, and from then on you press Refresh.

Import, clean and reshape data from files, folders, web pages and databases
Build queries that refresh themselves when the source changes
Merge and append tables without a single VLOOKUP
Every exercise file included, lifetime access, all future updates

30-day money-back guarantee, handled by Udemy. No forms, no questions.

★★★★★ Join 80,000+ students

Andreas Exadaktylos · Microsoft-certified Excel instructor · mellontraining.com