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.

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