Merging Formatting Correctly When Linking A Word Document To Excel

Question

Why does Word automatically insert line breaks before and after my merged data from Excel? I am trying to create a Word document that will automatically update based on data in an Excel spreadsheet. I would like the format to look like Example 1, but when I insert the data, it ends up looking like Example 2.

Example 1

Example 2

---------

I created the below Excel spreadsheet, copied the desired data, and pasted using the "Link & Merge Formatting (L)" option.

I can reformat the data to match my desired formatting, but whenever I update the data in Excel, the formatting reverts back to the incorrect format.

Below is the corresponding field code:

How can I link these documents so that these line breaks are not inserted every time the data updates?

Answer 1

Use the Paste Special menu item to open the dialog, choose the Paste Link option button, and then choose Unformatted Text in the list. This will give you the LINK field with a \t switch instead of the \h switch, and omit the \f switch.

That gives you the desired result,

Answer 2

Hi Evan,

I'm not sure why the CHAR(10) characters appear in Word as extra quotation marks, but the real problem is that in Word the line break character is ASCII 11, not 10.

When I change the formula of cell F7 in Excel to

=IF(B7=0,"",CHAR(11)&CHAR(11)&C7&D7&E7)

then the line break characters appear like this in the cell:

and copying that cell and pasting it into Word shows this (with nonprinting characters displayed):

Answer 3

Thanks, Jay! That did the trick.

Answer 4

Hi Jay,

Follow up question here. I am trying to insert a line breaking using the CHAR(10) formula. Do you know why extra quotation marks are being added around my data?

Answer 5

Great, thanks again!