Custom number formatting doesn't work when pasted in Word

Stidd

New Member
Joined
Dec 9, 2011
Messages
18
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
if you have ( £3.99 ) in cell A1 make AA1 = a text copy of A1 and link AA1 to the word document
 
Upvote 0
-3.99 changed to this by custom formatting
(3.99)(if d5<0 return an open bracket otherwise a ""
3.99
)if d5<0 return a close bracket otherwise a ""
(3.99)concatenate the above 3
this can linkto the word document

<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
(3.99)(3.99)
5.795.79
forumformatting makes it look wrong but decimal points are lined up

the 5.79 is formatted 0.00# where # = a space character

<tbody>
</tbody>
 
Upvote 0
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")
 
Last edited:
Upvote 0
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")
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top