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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
if you have ( £3.99 ) in cell A1 make AA1 = a text copy of A1 and link AA1 to the word document
 

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
-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>
 

Stidd

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

New Member
Joined
Dec 9, 2011
Messages
18

ADVERTISEMENT

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

MrExcel MVP
Joined
Jun 9, 2006
Messages
1,802
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

New Member
Joined
Dec 9, 2011
Messages
18

ADVERTISEMENT

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

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
(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>
 

snb_

Well-known Member
Joined
Nov 9, 2009
Messages
567
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:

Stidd

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

Watch MrExcel Video

Forum statistics

Threads
1,123,426
Messages
5,601,595
Members
414,462
Latest member
StageRiis

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
Top