|
The information in this article applies to:
- 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
|