Custom number formatting doesn't work when pasted in Word

[Stidd]

I've been pasting linked Excel ranges into a Word document for presentation.
In Excel, I've created a custom number format so that negative numbers have brackets but remain aligned with positive ones i.e. "#,##0.00_);(#,##0.00)". However, no matter what type of link or styling option I use, this custom format is not carried over into the Word document. Some other elements are but not the trailing space for the ) symbol.
I have found a page which describes how to create custom formats in Word (Formatting Currency (Microsoft Word)) but I can't make it work in Word 2013 and it doesn't meet my needs as I need the numbers linked to the Excel, not manually typed in.
Is there any way to achieve what I want? I'd appreciate any guidance.
Thanks in advance

 

[oldbrewer]

if you have ( £3.99 ) in cell A1 make AA1 = a text copy of A1 and link AA1 to the word document

 

[oldbrewer]

[TABLE="width: 896"]
<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"] -3.99 changed to this by custom formatting[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl24, align: right"](3.99)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl24"]([/TD]
[TD][/TD]
[TD="colspan: 5"]if d5<0 return an open bracket otherwise a ""[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD])[/TD]
[TD][/TD]
[TD="colspan: 5"]if d5<0 return a close bracket otherwise a ""[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](3.99)[/TD]
[TD][/TD]
[TD="colspan: 3"]concatenate the above 3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]this can linkto the word document[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Stidd]

Thanks for coming back to me OldBrewer,

I'm not sure what you mean by a 'text copy'? If I copy and paste values that doesn't work; nor does any copy and paste option. If I make AA1 =A1 but format as text, that doesn't work; nor does any formatting options applied to those cells.

Could you clarify?

if you have ( £3.99 ) in cell A1 make AA1 = a text copy of A1 and link AA1 to the word document

 

[Stidd]

The concatenation doesn't make the positive and negative numbers align on the decimal though. It creates the brackets but they come across into Word anyway. It is the trailing space in positive numbers, for the ")" in negative numbers, that doesn't come across.

 

[Cindy Ellis]

I'm not sure this will help, but if everything works except alignment on the decimal, you should be able to use a decimal-aligned-tab in Word. There are a couple of ways to do this...I usually have the top ruler turned on, and use the tab type icon to the left of the ruler (at the top) to cycle through the tab options. You want the one that looks like an upside-down T with a dot in it.

 

[Stidd]

Thanks Cindy, I know how to put a decimal tab stop into a Word line of text but I can't seem to apply them to numbers already in a Word table: Obviously when I press the tab key within a table cell it moves me along the row. I also don't think this will hold once the link back the Excel range is refreshed

 

[oldbrewer]

[TABLE="width: 640"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl24, align: right"](3.99)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl26"](3.99)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]5.79[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl25, align: right"]5.79[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]forumformatting makes it look wrong but decimal points are lined up

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]the 5.79 is formatted 0.00# where # = a space character[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[snb_]

Use in Word:

{ LINK Excel.SheetBinaryMacroEnabled.12 "G:\\OF\\__tabelkoppeling met Word.xlsb" "Sheet1!R1C1:R10C9" \a \f 4 \r }


You will have to produce the accolades using Ctrl-F9

You will have to replace "G:\\OF\\__tabelkoppeling met Word.xlsb" by the fullnamename of the workbook you are working with.

The same applies to the reference of the Table ("Sheet1!R1C1:R10C9")

 

[Stidd]

Hi Snb_

Thanks for you help. I can get the link to work but it still doesn't carry over the particular custom number formatting: That is the trailing ")" sized space after positive numbers to make them align with negative ones...

Use in Word:

{ LINK Excel.SheetBinaryMacroEnabled.12 "G:\\OF\\__tabelkoppeling met Word.xlsb" "Sheet1!R1C1:R10C9" \a \f 4 \r }


You will have to produce the accolades using Ctrl-F9

You will have to replace "G:\\OF\\__tabelkoppeling met Word.xlsb" by the fullnamename of the workbook you are working with.

The same applies to the reference of the Table ("Sheet1!R1C1:R10C9")