XL: Importing Word Tables Into Microsoft Excel

ID: Q103274

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Word 97 for Windows
Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0

SUMMARY

When you copy a Microsoft Word table into a Microsoft Excel sheet, wrapped text and return characters that are contained in a single table cell are placed in multiple cells, causing the table to display incorrectly.

To open the table in Microsoft Excel, convert the table in Microsoft Word to text, and then save it to the text file format.

MORE INFORMATION

Below is an example of how the table in Microsoft Word might look. The string "First First" is in the first row first column of the table. The string "First Second" and the two rows containing LONGLONG are all contained within the first row, second column, of the table.


__________________________________________________
|First First    |First Second        |First Third |
|               |LONGLONGLONGLONGLONG|            |
|               |LONGLONGLONGLONGLONG|            |
|_______________|____________________|____________|
|Second First   |Second Second       |Second Third|
|_______________|____________________|____________|

To import a Microsoft Word table with no hard or soft returns

Select a cell in the Microsoft Word table, and choose Select Table from the Table menu.

From the Table menu, choose Convert Table To Text.

Under Separate Text With, select the Tabs option and choose OK. The result should look similar to the following:


      First First     First Second
      LONGLONGLONGLONGLONGLONGLONGLONGLONGLONGLONG
      First Third
      Second First     Second Second     Second Third

Copy the result to a new document, and save the new document to the Text Only file format.

Switch to Microsoft Excel.

From the File menu, choose Open from the File menu. From the List Files Of Type list, select Text Files.

In Microsoft Excel version 3.0 or 4.0, choose the Text button and verify that the column delimiter is set to Tab.

From the File Names box, select the appropriate filename and choose OK.

In Microsoft Excel version 5.0, the Text Import Wizard appears. Do the following to continue importing the table:

In the Text Import Wizard - Step 1 of 3 dialog box, select the Delimited option and choose the Next button.

In the Text Import Wizard - Step 2 of 3 dialog box, select the Tab check box, and choose the Finish button.

To import a Microsoft Word table with hard or soft returns

Select a cell in the Microsoft Word table, and choose Select Table from the Table menu.

From the Edit menu, choose Replace.

In the Find What box, type "^p" (without the quotation marks) to find hard returns, or type "^l" (without the quotation marks) to find soft returns (line feed characters) NOTE: In Microsoft Word for Windows version 2.0, type "^n" (without the quotation marks) to find soft returns (line feed characters) .

In the Replace With box, type a vertical bar "|" (without the quotation marks). Choose the Replace All button. Choose No in the dialog that is displayed asking if you want to search the remainder of the document.

From the Table menu, choose Convert Table To Text.

Under Separate Text With, select the Tabs option and choose OK.

Copy the result to a new document, and save the new document to the Text Only file format.

Switch to Microsoft Excel.

From the File menu, choose Open. From the List Files Of Type list, choose Text Files.

In Microsoft Excel version 4.0, choose the Text button and verify that the column delimiter is set to Tab.

From the File Names list box choose the appropriate filename, and choose OK.

Microsoft Excel version 5.0

In Microsoft Excel version 5.0, the Text Import Wizard appears. Do the following to continue importing the table:

In the Text Import Wizard - Step 1 of 3 dialog box, select the Delimited option and choose the Next button.

In the Text Import Wizard - Step 2 of 3 dialog box, select the Tab check box, and choose the Finish button.

Select the columns that contain the vertical bar (|), and choose Cells from the Format menu.

Select the Alignment tab. Select the Wrap Text check box, and choose OK.

Use the following command in a Visual Basic macro to convert the vertical bars (|) to line feed characters:

Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."


   Sub ReplacePipes()
      Selection.Replace What:="|", Replacement:=Chr(10), LookAt:=xlPart, _
         SearchOrder:=xlByRows
   End Sub

To insert a Visual Basic module into a workbook, click the Insert menu, point to Macro, and click Module.

Microsoft Excel version 4.0

Select the columns that contain the vertical bar (|), and choose Alignment from the Format menu.

Select the Wrap Text check box, and choose OK.

Use the following macro to convert the vertical bars (|) to line feed characters

A1: =FORMULA.REPLACE("|",CHAR(10),2,1,FALSE,FALSE)
A2: =RETURN()

where A1: Replaces the pipe symbols (|) with CHAR(10)--Char(10) is the macro equivalent for a line feed character--and A2: ends the macro.

REFERENCES

"Function Reference," version 4.0, pages 49, 174
"Function Reference," version 3.0, pages 29, 94

Additional query words: 2.00 4.00a 6.00 6.00a Officeinterop paste

Keywords :
Version : 3.00 4.00 4.00a 5.00
Platform : WINDOWS
Issue type :