I'm trying to automatically update some text boxes in Word using an Excel sheet, i.e. copying a cell in Excel, right-clicking in the text box in word and choosing the 'Link & Merge Formatting' option.
The link works, but inserts 2 blank spaces/invisible characters before the actual data i want to appear.
Anyone have any idea what's going on? I need to make these boxes pretty small, but in order to view the data i have to extend the box down to accomodate these 2 unwanted lines :(
I would suggest you go to Home tab > Clipboard group > The expand symbol of Paste > Paste Special > Paste link, Formatted Text (RTF).
My copies of Excel and Word yield a LF/CR at the end so a single line entry in the cell give two lines in the box. Your extra characters might be hugely different in their very nature but I doubt it. So I believe the following would work nicely for you.
Copy the cell, then RIGHT-click in the text box. (You have to click one way or the other so this adds no work to your process. Well, other than getting used to doing it.)
The right-click menu will appear. I would press the T key at this moment which would paste Text Only so only the Excel cell content would paste. Or, since you have the mouse in hand already, you could just click that button in the paste options (it is the rightmost one). Whichever seems more natural to you. I'm more accurate punching that T than rolling over to a button of many to click it.
That would be a fast way to get your result.
The source of the issue is that when you copy a cell, Excel lards it up with, for me, a LF/CR. Very obnoxious when doing this on websites that allow more than the first line in the pasting data but not a problem on websites that only allow the first line.
To bypass all that, you COULD... edit the cell, copying the text/number from inside the cell from the formula editing bar. So... click the cell, roll the mouse up to the formula bar and click at the content start or end then press shift while mousing, perhaps, to the other end, then copying and pressing Escape... that's the only way Excel puts ONLY the content on the clipboard.
But that'd be so very obnoxious vs. just right-clicking in the text box instead of left-clicking, and pressing T. So...
Note that in that menu, Cut at the top would be the first place T would take you, then to this button on a second press. But it seems that Cut will not be available under these circumstances so you press it once and your Paste|Special|Text Only button is the only place T goes so you get just the one keypress to deal with.
So, Right-click, press T, and move along. Actually easier than left-clicking then pressing the Ctrl-C combos, though since that's a habit of years, certainly, they might run neck and neck.
