You are currently offline, waiting for your internet to reconnect

XL97: Importing Word Tables Into Microsoft Excel

This article was previously published under Q103274
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
SUMMARY
When you copy a Microsoft Word table into a Microsoft Excel sheet, wrappedtext and return characters that are contained in a single table cell areplaced in multiple cells, causing the table to display incorrectly.

To open the table in Microsoft Excel, convert the table in MicrosoftWord 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. Thestring "First First" is in the first row first column of the table.The string "First Second" and the two rows containing LONGLONG are allcontained 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

  1. Select a cell in the Microsoft Word table, and choose Select Table from the Table menu.
  2. From the Table menu, choose Convert Table To Text.
  3. 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
  4. Copy the result to a new document, and save the new document to the Text Only file format.
  5. Switch to Microsoft Excel.
  6. From the File menu, choose Open from the File menu. From the List Files Of Type list, select Text Files.
  7. In Microsoft Excel version 3.0 or 4.0, choose the Text button and verify that the column delimiter is set to Tab.
  8. From the File Names box, select the appropriate filename and choose OK.
In Microsoft Excel version 5.0, the Text Import Wizard appears. Do thefollowing to continue importing the table:

  1. In the Text Import Wizard - Step 1 of 3 dialog box, select the Delimited option and choose the Next button.
  2. 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

  1. Select a cell in the Microsoft Word table, and choose Select Table from the Table menu.
  2. From the Edit menu, choose Replace.
  3. 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) .
  4. 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.
  5. From the Table menu, choose Convert Table To Text.
  6. Under Separate Text With, select the Tabs option and choose OK.
  7. Copy the result to a new document, and save the new document to the Text Only file format.
  8. Switch to Microsoft Excel.
  9. From the File menu, choose Open. From the List Files Of Type list, choose Text Files.
  10. In Microsoft Excel version 4.0, choose the Text button and verify that the column delimiter is set to Tab.
  11. 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 thefollowing to continue importing the table:

  1. In the Text Import Wizard - Step 1 of 3 dialog box, select the Delimited option and choose the Next button.
  2. In the Text Import Wizard - Step 2 of 3 dialog box, select the Tab check box, and choose the Finish button.
  3. Select the columns that contain the vertical bar (|), and choose Cells from the Format menu.
  4. Select the Alignment tab. Select the Wrap Text check box, and choose OK.
  5. 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 illustrationonly, without warranty either expressed or implied, including but notlimited to the implied warranties of merchantability and/or fitness for aparticular purpose. This Visual Basic procedure is provided 'as is' andMicrosoft does not guarantee that it can be used in all situations.Microsoft does not support modifications of this procedure to suit customerrequirements for a particular purpose. Note that a line that is preceded byan apostrophe introduces a comment in the code--comments are provided toexplain what the code is doing at a particular point in the procedure. Notealso that an underscore character (_) indicates that code continues fromone line to the next. You can type lines that contain this character as onelogical line or you can divide the lines of code and include the linecontinuation character. For more information about Visual Basic forApplications programming style, see the "Programming Style in This Manual"section in the "Document Conventions" section of the "Visual Basic User'sGuide."

   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

  1. Select the columns that contain the vertical bar (|), and choose Alignment from the Format menu.
  2. Select the Wrap Text check box, and choose OK.
  3. 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
2.00 4.00a 6.00 6.00a Officeinterop paste
Properties

Article ID: 103274 - Last Review: 01/18/2007 22:41:05 - Revision: 1.1

  • Microsoft Excel 97 Standard Edition
  • Microsoft Word 97 Standard Edition
  • kbinfo KB103274
Feedback