How to Convert a Word or Excel Table to Text
The Table object in Excel and Word is underutilized by most users and developers of Excel and Word, in my not-so-humble opinion. For example, an Excel Table object with formulas calculates faster and takes less storage space than does the comparable normal range. As useful as tables are in Excel and Word, there are times you prefer normal text.
This article explains how to convert a table to normal text manually and using VBA for both Excel and Word. These instructions apply to Office 365/Office 2019 for Windows. These instructions have not changed much from legacy versions of Word and Excel for Windows and Mac OS since 2007.
Contents
Convert an Excel Table to a Normal Range
The steps to convert a Table object to text are almost the same for Word and Excel.
1. Position the cursor anywhere in the table. This is usually done by clicking a cell.
2. From the Layout tab, choose Convert to Range (Tools group).
3. Choose Yes to confirm.
Convert a Word Table to Text
Like Excel, a table cell in Word is the intersection of a row and column, as shown here.
1. Position the cursor anywhere in the table. This is usually done by clicking text.
2. From the Layout tab, choose Convert to Text (Data group).
3. Choose how to separate the text between table cells.
3a. If you choose Tabs, the table layout is preserved with tab stops between cells, as shown. This option also works for saving a text file with Tab as the delimiter.
3b. If you choose Paragraphs, the text forms a single list, starting with the left column.
3c.The choice Commas resembles Tabs, except commas are inserted instead of tabs. Choose this option to save the text as a CSV file (Comma-Separated Values). The choice Other gives you a similar result. The character you choose becomes the delimiter.
Convert an Excel Table to a Normal Range Using VBA
The Record Macro command does not record converting a table to a normal range, so you have to type it yourself. The most simple approach is to add one statement to your procedure. The statement uses the Unlist
method of the ListObjects
collection. Reference the table by number to convert the first or only table on the active sheet:
ActiveSheet.ListObjects(1).Unlist
Reference the table by name to convert one table on the active sheet with more than one table. Use the name of the table shown on Table Design tab (Properties group):
ActiveSheet.ListObjects(“MyTable”).Unlist
You can convert all the tables on the active sheet to a range using this procedure:
Sub ConvertTablesToRange() Dim ws As Worksheet Dim tbl As ListObject Set ws = ActiveWorkbook.ActiveSheet For Each tbl In ws.ListObjects tbl.Unlist Next End Sub
Adding a nested For-Next loop converts all the tables in the active workbook:
Sub ConvertTablesToRange() Dim ws As Worksheet Dim tbl As ListObject For Each ws In ActiveWorkbook.Worksheets For Each tbl In ws.ListObjects tbl.Unlist Next Next End Sub
Convert a Word Table to Text Using VBA
The VBA code to convert a Word table object is similar to that of Excel. The comparable statement uses the ConvertToText
method of the Tables
collection.
ActiveDocument.Range.Tables(1).ConvertToText Separator:=wdSeparateByTabs
Word does not name its tables, so manually assign a bookmark to the table if you have to use VBA to convert a specific table to text. To assign a bookmark, first position the cursor in a table cell. From the Insert tab, choose the Links down-arrow button, Bookmark (Step 1, below). For Bookmark Name, type a unique name with no spaces and choose Add (Step 2).
Step 1. Bookmark command
Step 2. Add new bookmark
Here is the statement to convert a “named” table in a document to text. Tables(1) refers to the first table in the range of the “MyTable” bookmark, not the first table in the document.
ActiveDocument.Bookmarks("MyTable").Range.Tables(1).ConvertToText Separator:=wdSeparateByTabs
The VBA code to convert all the tables in a Word document resembles the code to convert all the tables on an Excel worksheet:
Sub ConvertTablesToText() Dim tbl As Table For Each tbl In ActiveDocument.Tables tbl.ConvertToText Separator:=wdSeparateByTabs Next End Sub
Remember to include the Separator parameter, unless you want to accept the default delimiter of hyphen (-):
wdSeparateByTabs wdSeparateByCommas wdSeparateByParagraphs wdSeparateByDefaultListSeparator