Maintaining word format when linking text from excel

[MikeFee]

I am attempting to produce a document in which, at a number of points, I use Links to an excel spread sheet to allow me to update the content of the document. The update data is in the form of numbers and short strings of text.
Overall the system appears to work but I am having difficulty maintaining the local text formatting in the word document when I update the links.

As an example, if I have a sentence "I had toast for breakfast.", where 'toast' is the linked text, and the sentence is in 24 point Times New Roman, then if I update the link in excel to 'toast and coffee', the text in word updates but the format now switches to the format used in the excel spread-sheet for the words 'and coffee' so it looks like: "I had toast and coffee for breakfast".

I can 'fix' this, by converting the whole sentence to Times New Roman, selecting the link in the 'preserve formatting after update' in the Links pop-up box, and things are fine if I change 'toast and coffee' to 'bacon and eggs' for example so "I had toast and coffee for breakfast" changes to "I had bacon and eggs for breakfast"

But if I add an extra word to the text in excel 'toast and black coffee' then when it updates, the final word is in the excel format rather than in the 'local' word format. i.e. "I had toast and black coffee for breakfast".

Note that to add the text I select the data in excel, select paste special, click on the Paste Link button and select 'Unformatted text'.



Is there a way to force all the added text to conform to the surrounding, or existing, format in word, when the number of words (or spaces) in the linked text changes?

So far the only way I can get it to work is to set the excel format for the particular link to the same format as the section of the word document I want to update and then use the 'Formatted text' option, but this requires adjusting the font of each excel cell individually and fails if the word format is changed at some time in the future without making a similar chance to the cell. Note that if I select text from a text-based app like Notepad and paste it into word, then all the pasted text matches the local text format.