| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mail Merge and Incorrect Formatting
Hi,
I merge my letter (word) with Excel (data source). It runs fine, but the formatting of the number is changed in the mail merge's result. Ex: in Excel under COST column, it was 322,756, but it is shown 322755.5 in the final letter. In the Mail Merge Recipients window, I also see the number's formatting changed in there. Please help Thanks Chi |
|
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Mail Merge and Incorrect Formatting
Hi Chi,
What you're seeing is what is actually stored in the Excel cell. 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; .. run your mailmerge. 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 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. -- Cheers macropod [Microsoft MVP - Word] "Chi" wrote in message ... Hi, I merge my letter (word) with Excel (data source). It runs fine, but the formatting of the number is changed in the mail merge's result. Ex: in Excel under COST column, it was 322,756, but it is shown 322755.5 in the final letter. In the Mail Merge Recipients window, I also see the number's formatting changed in there. Please help Thanks Chi |
|
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Mail Merge and Incorrect Formatting
Hi Macropod,
Excellent! Thank you very much! The problem is fixed! Would you please also show me how to fix the % formatting? In Excel is -17.0% and it appeared -0.1695323549869793 in Word Thank you! Chi "macropod" wrote: Hi Chi, What you're seeing is what is actually stored in the Excel cell. 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; .. run your mailmerge. 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 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. -- Cheers macropod [Microsoft MVP - Word] "Chi" wrote in message ... Hi, I merge my letter (word) with Excel (data source). It runs fine, but the formatting of the number is changed in the mail merge's result. Ex: in Excel under COST column, it was 322,756, but it is shown 322755.5 in the final letter. In the Mail Merge Recipients window, I also see the number's formatting changed in there. Please help Thanks Chi |
|
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Mail Merge and Incorrect Formatting
Hi Chi,
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; .. run your mailmerge. As per my previous post, the number of 0s after the decimal point controls the precision. -- Cheers macropod [Microsoft MVP - Word] "Chi" wrote in message ... Hi Macropod, Excellent! Thank you very much! The problem is fixed! Would you please also show me how to fix the % formatting? In Excel is -17.0% and it appeared -0.1695323549869793 in Word Thank you! Chi "macropod" wrote: Hi Chi, What you're seeing is what is actually stored in the Excel cell. 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; .. run your mailmerge. 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 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. -- Cheers macropod [Microsoft MVP - Word] "Chi" wrote in message ... Hi, I merge my letter (word) with Excel (data source). It runs fine, but the formatting of the number is changed in the mail merge's result. Ex: in Excel under COST column, it was 322,756, but it is shown 322755.5 in the final letter. In the Mail Merge Recipients window, I also see the number's formatting changed in there. Please help Thanks Chi |
|
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Mail Merge and Incorrect Formatting
I tried your suggestions below and they work fine.
Another solution ... Copy all the necessary data from Excel into a new Word file. (All the data is copied as text, but is also copied in the correct number, percentage or date formats. Mark the top row as the header then save the file. Open Word document for merging and select the new data file. This works too without having to recode every field in the merge document. -- Thanks "macropod" wrote: Hi Chi, What you're seeing is what is actually stored in the Excel cell. 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; .. run your mailmerge. 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 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. -- Cheers macropod [Microsoft MVP - Word] "Chi" wrote in message ... Hi, I merge my letter (word) with Excel (data source). It runs fine, but the formatting of the number is changed in the mail merge's result. Ex: in Excel under COST column, it was 322,756, but it is shown 322755.5 in the final letter. In the Mail Merge Recipients window, I also see the number's formatting changed in there. Please help Thanks Chi |
|
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Mail Merge and Incorrect Formatting
Good Morning Macropod,
It woks wonderful! Sorry I have more questions. In my % field, there is some "N\A" text so that It got some €œ! Undefined Bookmark,N". Would you please show me how to make it displays N\A instead of "! Undefined Bookmark,N". Thanks Chi "macropod" wrote: Hi Chi, 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; .. run your mailmerge. As per my previous post, the number of 0s after the decimal point controls the precision. -- Cheers macropod [Microsoft MVP - Word] "Chi" wrote in message ... Hi Macropod, Excellent! Thank you very much! The problem is fixed! Would you please also show me how to fix the % formatting? In Excel is -17.0% and it appeared -0.1695323549869793 in Word Thank you! Chi "macropod" wrote: Hi Chi, What you're seeing is what is actually stored in the Excel cell. 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; .. run your mailmerge. 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 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. -- Cheers macropod [Microsoft MVP - Word] "Chi" wrote in message ... Hi, I merge my letter (word) with Excel (data source). It runs fine, but the formatting of the number is changed in the mail merge's result. Ex: in Excel under COST column, it was 322,756, but it is shown 322755.5 in the final letter. In the Mail Merge Recipients window, I also see the number's formatting changed in there. Please help Thanks Chi |
|
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Mail Merge and Incorrect Formatting
Hi dccsvd55,
Sorry, I don't kow your name. Your way also works wonderful!!! Thank you for sharing it . Chi "" wrote: I tried your suggestions below and they work fine. Another solution ... Copy all the necessary data from Excel into a new Word file. (All the data is copied as text, but is also copied in the correct number, percentage or date formats. Mark the top row as the header then save the file. Open Word document for merging and select the new data file. This works too without having to recode every field in the merge document. -- Thanks "macropod" wrote: Hi Chi, What you're seeing is what is actually stored in the Excel cell. 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; .. run your mailmerge. 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 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. -- Cheers macropod [Microsoft MVP - Word] "Chi" wrote in message ... Hi, I merge my letter (word) with Excel (data source). It runs fine, but the formatting of the number is changed in the mail merge's result. Ex: in Excel under COST column, it was 322,756, but it is shown 322755.5 in the final letter. In the Mail Merge Recipients window, I also see the number's formatting changed in there. Please help Thanks Chi |
|
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Mail Merge and Incorrect Formatting
Use an If...then...Else field construction to test for the value in the
field. { IF { MERGEFIELD PROD } = "N/A" "N/A" "{ = { MERGEFIELD PROD } * 100 \# "0%" }" } -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Chi" wrote in message ... Good Morning Macropod, It woks wonderful! Sorry I have more questions. In my % field, there is some "N\A" text so that It got some €œ! Undefined Bookmark,N". Would you please show me how to make it displays N\A instead of "! Undefined Bookmark,N". Thanks Chi "macropod" wrote: Hi Chi, 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; .. run your mailmerge. As per my previous post, the number of 0s after the decimal point controls the precision. -- Cheers macropod [Microsoft MVP - Word] "Chi" wrote in message ... Hi Macropod, Excellent! Thank you very much! The problem is fixed! Would you please also show me how to fix the % formatting? In Excel is -17.0% and it appeared -0.1695323549869793 in Word Thank you! Chi "macropod" wrote: Hi Chi, What you're seeing is what is actually stored in the Excel cell. 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; .. run your mailmerge. 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 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. -- Cheers macropod [Microsoft MVP - Word] "Chi" wrote in message ... Hi, I merge my letter (word) with Excel (data source). It runs fine, but the formatting of the number is changed in the mail merge's result. Ex: in Excel under COST column, it was 322,756, but it is shown 322755.5 in the final letter. In the Mail Merge Recipients window, I also see the number's formatting changed in there. Please help Thanks Chi |
|
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Mail Merge and Incorrect Formatting
This advice was a lifesaver.
Thanks "macropod" wrote: Hi Chi, 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; .. run your mailmerge. As per my previous post, the number of 0s after the decimal point controls the precision. -- Cheers macropod [Microsoft MVP - Word] "Chi" wrote in message ... Hi Macropod, Excellent! Thank you very much! The problem is fixed! Would you please also show me how to fix the % formatting? In Excel is -17.0% and it appeared -0.1695323549869793 in Word Thank you! Chi "macropod" wrote: Hi Chi, What you're seeing is what is actually stored in the Excel cell. 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; .. run your mailmerge. 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 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. -- Cheers macropod [Microsoft MVP - Word] "Chi" wrote in message ... Hi, I merge my letter (word) with Excel (data source). It runs fine, but the formatting of the number is changed in the mail merge's result. Ex: in Excel under COST column, it was 322,756, but it is shown 322755.5 in the final letter. In the Mail Merge Recipients window, I also see the number's formatting changed in there. Please help Thanks Chi |
| Reply |
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Mail Merge Field-Format Incorrect | Mailmerge | |||
| In MAIL MERGE I get a record # that is incorrect? | Mailmerge | |||
| Number of pages incorrect while doing mail merge | Mailmerge | |||
| incorrect date format in mail merge | Mailmerge | |||
| Mail Merge Incorrect | Mailmerge | |||
Linear Mode
