Request a callback

We'll call during UK business hours

Name:
Number:
0207 987 3777

previousPrevious article   Next articlenext back to categoryWord articles

article How To Export A Word Table To Excel

Ever needed to export a Word table into Excel? Read on to discover three different ways to do this and preserve your original table layout.
If you accumulate lots of data in Word tables, or you use lots of Word tables to list data records, you may at some point want to export all this data into Excel. This article describes several ways to do this depending on how much data you want to export.

If you try to open a Word file in Excel and you'll be faced with an error message such as "file format is not valid" as Excel only recognises data formats such as txt or csv (comma separated values) files which allow storing data without any extra formatting. So if you want Excel to recognise your Word file you need to change the file type to a text file when saving. However if you save a Word file containing a table as a text file, you will loose the table formatting, and Excel will only import the table data as one huge list with each data item on a separate line. Before looking at ways to save a Word table in a format Excel will recognise, we'll first look at copy and paste.

A surprisingly effective way to export a table from Word to Excel is to copy and paste the table from Word to Excel. So in Word you first select the table, choose copy, switch to Excel and then choose paste. Excel will create a correctly structured table and applying border formatting round all the data cells. If you don't want the border formatting in Excel you can choose paste special and paste the data only. Copy and paste works well if you only want to convert one table. However if the Word data is split over two or more tables then the following two methods may also be useful. These are converting table to text, and mail merging.

Firstly, you can convert your table to columns of text within Word using the table to text feature, and then save the file as a text file. When you do this Word will add tab characters between the data so you'll still retain the table like structure but without the Word table formatting. If you then save the Word file as a text file the data will remain in columns, making it easy for Excel to import. You can use table to text to convert more than one table of data.

To use text to table to create the conversion in Word, first select the table, then choose table, convert, table to text. In the convert table to text panel choose which character to use to separate the columns, for example tab, then click OK to finish. If the table contains many columns then a space character may be a better choice in order to keep the total table width under control. The data will now show in tabular layout but without the table formatting. Then save the Word file as a text file.

In Excel, choose open file, and choose all file types. Then navigate to the saved text file and double click it to load it. Excel's recognises the txt file format and the import file wizard will appear. In the import wizard choose the correct data separator character such as tab or space you chose earlier, and you'll find that Excel then correctly previews the columns of data. If the preview does not correctly show the data columns, try going back a step in the wizard and change the separator character, for example from a tab to a space. Once the data previews correctly you can complete the wizard and the data will be imported in Excel in the original columns layout.

Secondly, you can create columns of data from the Word table by using Word's mail merge feature. Using this you can create a new document containing columns of data from the original Word table. You can also use mail merge to convert more than one table of data into a final list but you'll need to run mail merge again for each table, changing the data source each time.

To use mail merge to create the conversion in Word, suppose you have a Word table saved as table1. To use mail merge to convert your data, open a new Word file then start "Mail Merge". Choose the data source as list1 and the new document as the current newly opened document. Start the mail merge process but rather than choosing the traditional letter or envelope option, choose to merge to a directory, which will create a list of data in columns corresponding to the original table layout. Choose the "Insert Merge Field" buttons to add the table headings separated by spaces across the top of your merge document, and press the enter key at the end of the line to place the cursor in a new line. It's important to press enter key at the end of the headings in order that each line of data starts on a new line.

Then click the "Merge to New Document" button, and Word will create a columnised list from the original table, which you can then save as a text file before importing into Excel.

So you can export a table from Word to Excel using copy and paste, text to columns or mail merge to a directory. All these methods retain the data structure. Hopefully this article will give you a brief insight into how to import Word tables into Excel. To find out much more about these and many other Word and Excel features, you might consider attending a training course and boost your skills further.

Author is a freelance copywriter. For more information on word training london, please visit http://www.microsofttraining.net


Original article appears here:
http://www.microsofttraining.net/article-1347-how-export-word-table-excel.html


Back to article list

Distribution notes

PUBLICATION GUIDELINES

  • You have permission to publish this article for free providing the "About the Author" box is included in its entirety.
  • Do not post/reprint this article in any site or publication that contains hate, violence, porn, warez, or supports illegal activity.
  • Do not use this article in violation of the US CAN-SPAM Act. If sent by email, this article must be delivered to opt-in subscribers only.
  • If you publish this article in a format that supports linking, please ensure that all URLs and email addresses are active links, without the rel='nofollow' tag.
  • Software Training London Ltd. owns this article. Please respect the author's copyright and above publication guidelines.
  • If you do not agree to these terms, please do not use this article.


Microsoft Office courses London
Course rating:
4.7 stars - based on 829 reviews
Microsoft Certified Partner Institute of Leadership and Management - Certified Courses Learning and Performance Institute - Accredited Training Provider Security Seal verified by visa, mastercard securecard

Connect with us: Google+ · Facebook · Twitter · LinkedIn · Pinterest

2nd Floor, CA House, 1 Northey Street
Limehouse Basin, London, E14 8BT
United Kingdom