Daljit Dhadwal

Every once in a while, a situation comes up where there’s a need to copy a table made in Microsoft Word to Excel. If the Word table has multiple lines of text within a cell that have been separated by a carriage return (i.e., the Enter key has been typed at the end of a line of text), then the table won’t properly copy over. When Excel encounters a carriage return, the cursor will move into a new row.

For example, in the following Word table, the Enter key has been typed at the end of every line in the address column.

If you copy the above into Excel, you’ll get this:

The city and postal codes are copied into their own cells.
Data like this is hard to work with (e.g., you can’t sort this data).

Here’s how to solve this problem.

In Word replace the paragraph markers (i.e., the Enter key character) with some other symbol. For example, use ZZ.

In Word, type Ctrl H to open the Replace dialog box. Make sure to press the more button to see the advanced options.

The table in Word will now look this:

Copy this table into Excel.

Now, each person record only takes up one row. Next we just have to replace the ZZ with line breaks. In Excel type Ctrl H to open the Replace dialog box. You’ll need to type in the code for the new line character using the numeric keypad. Make sure the Num Lock key has been pressed.

Here’s what the final result looks like in Excel.