Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Mail Merge Formating Issues
I hope this is a simple fix. I am merging some data from Excel into a form
built in Word. I have fomated the cells in Excel but when the merge to Word they are not coming out correctly. EX - Number are formated in Excel with commas but merge to Word with no commas and extend decimal places. Dates are formated in Excel as July 13, 2009 but merge to Word as 071309. What do I need to do to get this merge to format properly in Word? Thanks so much for your help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Mail Merge Formating Issues
I can't tell your exact steps you are peforming. You may be able to solve
the problem by copying the data and using PasteSpecial choosing text. You may want to copy the data first to a new worksheet using the paste special method and then performing the mail merge from the new worksheet instead of the origianl worksheet. "DataGuy" wrote: I hope this is a simple fix. I am merging some data from Excel into a form built in Word. I have fomated the cells in Excel but when the merge to Word they are not coming out correctly. EX - Number are formated in Excel with commas but merge to Word with no commas and extend decimal places. Dates are formated in Excel as July 13, 2009 but merge to Word as 071309. What do I need to do to get this merge to format properly in Word? Thanks so much for your help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Mail Merge Formating Issues
Hi DataGuy,
To control number & currency formatting 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. Note: The '\# $,0.00' 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;($,0.00);'-' for currency, with brackets around negative numbers and a hyphen for 0 values The precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'. To control percentage formatting in Word, add 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.00%}; .. position the cursor anywhere in this field and press F9 to update it. Note: the precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'. To get the date format you want, you can add a formatting picture switch. In Word: .. 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. .. position the cursor anywhere in this field and press F9 to update it. When you're done with the above, run your mailmerge. -- Cheers macropod [Microsoft MVP - Word] "DataGuy" wrote in message ... I hope this is a simple fix. I am merging some data from Excel into a form built in Word. I have fomated the cells in Excel but when the merge to Word they are not coming out correctly. EX - Number are formated in Excel with commas but merge to Word with no commas and extend decimal places. Dates are formated in Excel as July 13, 2009 but merge to Word as 071309. What do I need to do to get this merge to format properly in Word? Thanks so much for your help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Mail Merge Formating Issues
Thanks Guys this was a VERY BIG help - I had spent day's trying to get it right
"macropod" wrote: Hi DataGuy, To control number & currency formatting 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. Note: The '\# $,0.00' 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;($,0.00);'-' for currency, with brackets around negative numbers and a hyphen for 0 values The precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'. To control percentage formatting in Word, add 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.00%}; .. position the cursor anywhere in this field and press F9 to update it. Note: the precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'. To get the date format you want, you can add a formatting picture switch. In Word: .. 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. .. position the cursor anywhere in this field and press F9 to update it. When you're done with the above, run your mailmerge. -- Cheers macropod [Microsoft MVP - Word] "DataGuy" wrote in message ... I hope this is a simple fix. I am merging some data from Excel into a form built in Word. I have fomated the cells in Excel but when the merge to Word they are not coming out correctly. EX - Number are formated in Excel with commas but merge to Word with no commas and extend decimal places. Dates are formated in Excel as July 13, 2009 but merge to Word as 071309. What do I need to do to get this merge to format properly in Word? Thanks so much for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mail merge formating | New Users to Excel | |||
Code launches Mail Merge but disables the Mail Merge | Excel Discussion (Misc queries) | |||
mail merge excludes my headers and critical data in Word merge | Excel Discussion (Misc queries) | |||
how do i get my mail merge to update the data source at each merge | Excel Discussion (Misc queries) | |||
formating issues when printing using different printers | Setting up and Configuration of Excel |