Mail Merge and Incorrect Formatting

[Chi]

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

 

[macropod]

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.

 

[Chi]

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

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]

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

 

[macropod]

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]

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

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]

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

 

[dccsvd55]

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

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]

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

 

[Chi]

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

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]

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

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]


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

 

[Chi]

Hi dccsvd55,

Sorry, I don't kow your name. Your way also works wonderful!!! Thank you for
sharing it .

Chi

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

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]

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

 

[Doug Robbins - Word MVP]

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

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

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]

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

:

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]


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

 

[Caykiwi]

This advice was a lifesaver.
Thanks

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]

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

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]


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