Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merge Docunent
I have created a merge document in word, pulling data from an excell
spreadsheet. When it populates the document the dollar amounts are not formatted as they are in excell, they have 16 characters after the decimal point instead of 2. Does anyone know how to correct this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merge Docunent
Hi carter,
In Word, add a numeric picture switch to the mergefield. To do this: .. select the field; .. press Shift-F9 to reveal the field coding. It should look something like {MERGEFIELD MyData}; .. edit the field so that you get {MERGEFIELD MyData \# $,0.00} (or whatever other numeric format you prefer - see below); .. position the cursor anywhere in this field and press F9 to update it; .. run your mailmerge. Note: The '\# 0' in the field is referred to as a numeric picture switch. Other possibilities include: .. \# 0 for rounded whole numbers .. \# ,0 for rounded whole numbers with a thousands separator .. \# ,0.00 for numbers accurate to two decimal places, with a thousands separator .. \# $,0 for rounded whole dollars with a thousands separator .. \# 0.00% for percentages .. \# $,0.00;($,0.00);'-' for currency, with brackets around negative numbers and a hyphen for 0 values The precision of the displayed value is controilled by the '0.00'. You can use anything from '0' to '0.000000000000000'. If you use a final ';' in the formatting switch with nothing following, (eg \# $,0.00;($,0.00);) zero values will be suppressed. Note that this suppresses 0s resulting from empty fields and from fields containing 0s. For mailmerge percentages, you may find that the value appears in Word as a 0 followed by a decimal number (eg 0.12). Adding the '\# 0.00%' switch would return '0.12%' instead of '12%'. To deal with this, you'll need to add both a formula and numeric picture switch to the mergefield. To do this: .. select your mergefield, which will look something like «Percent»; .. press Ctrl-F9 to wrap another field around it, thus - { «Percent» }; .. edit the field so that you get {=«Percent»*100 \# 0%}; .. position the cursor anywhere in this field and press F9 to update it; To get the date format you want, you can add a formatting picture switch to the mergefield. To do this: .. select the mergefield; .. press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's name; .. delete anything appearing after the mergefield's name and add '\@ "d MMMM yyyy"' to the field, as in {MERGEFIELD MyDate \@ "d MMMM yyyy"}. With this switch your date will come out as '2 August 2008'. Other possible date formatting switches include: . \@ "dddd, d MMMM yyyy"; . \@ "ddd, d MMMM yyyy"; . \@ "d MMM yyyy"; . \@ "dd/MMM/yyyy"; . \@ "d-MM-yy"; Note: Note: you can swap the d, M, y expressions around, but you must use uppercase 'M's for months. -- Cheers macropod [Microsoft MVP - Word] "carter" wrote in message ... I have created a merge document in word, pulling data from an excell spreadsheet. When it populates the document the dollar amounts are not formatted as they are in excell, they have 16 characters after the decimal point instead of 2. Does anyone know how to correct this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merge Docunent
Thank you very much! I really appreciate the help!
"carter" wrote: I have created a merge document in word, pulling data from an excell spreadsheet. When it populates the document the dollar amounts are not formatted as they are in excell, they have 16 characters after the decimal point instead of 2. Does anyone know how to correct this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to merge from excel to word. New names won't merge | Excel Worksheet Functions | |||
Merge option is not available. How to undo merge in this case? | Excel Discussion (Misc queries) | |||
mail merge excludes my headers and critical data in Word merge | Excel Discussion (Misc queries) | |||
Merge =( formula should retain fraction type numbers after merge. | Excel Worksheet Functions | |||
how do i get my mail merge to update the data source at each merge | Excel Discussion (Misc queries) |