mail merging formatiing

When mail merging from an excel file to a word document, how do I keep the
number or currency format? It keeps merging as a number of varying decimal
points although it's formatting correctly in excel..

You can do that by adding a numeric picture switch to the mergefield. To
achieve a two-decimal currency result:
.. select the Word MERGEFIELD and press Shift-F9. You should see something
like-
{MERGEFIELD MyData}
.. edit the Word MERGEFIELD to look like-
{MERGEFIELD MyData \# $,0.00} or {MERGEFIELD MyData \# £,0.00}, etc,
depending on the required currency format;
.. press F9;
.. run your mailmerge.

See Word's help file for more information on field formatting


Cheers


"registrarirene" wrote in message
...
When mail merging from an excel file to a word document, how do I keep the
number or currency format? It keeps merging as a number of varying decimal
points although it's formatting correctly in excel..

I have had the same problem. What you need to do is to put a function into a
module in excl. You can then use the function to change the cell to only have
2 decimal points. The problem is that in excel even though you only see 2 dec
places there is really all decimal places still existing and these are
transferred to word.
Below is module you can use.
It has worked for me.

Function dec_sorter(ByRef number As Double) As String
Dim s() As String
Dim temp As String
temp = CStr(number)

If InStr(temp, ".") 0 Then
s = Split(CStr(number), ".")
dec_sorter = s(0) & "." & Left(s(1), 2)
Else
dec_sorter = CStr(number)
End If
End Function

Regards, Hope it works.

"registrarirene" wrote:

When mail merging from an excel file to a word document, how do I keep the
number or currency format? It keeps merging as a number of varying decimal
points although it's formatting correctly in excel..

I am merging a date field from a .DBF database file. The field in the file is
06/27/84. On my document I have the data file field as ONLINEDATE which is
the field name in the file.

What I would like to do is have it appear in my document as "June 27, 1984"
without the quotes.

If you can answer this, please be as detailed as possible.

TIA for your help. If possible, please also send the answer to




"macropod" wrote:

You can do that by adding a numeric picture switch to the mergefield. To
achieve a two-decimal currency result:
.. select the Word MERGEFIELD and press Shift-F9. You should see something
like-
{MERGEFIELD MyData}
.. edit the Word MERGEFIELD to look like-
{MERGEFIELD MyData \# $,0.00} or {MERGEFIELD MyData \# £,0.00}, etc,
depending on the required currency format;
.. press F9;
.. run your mailmerge.

See Word's help file for more information on field formatting


Cheers


"registrarirene" wrote in message
...
When mail merging from an excel file to a word document, how do I keep the
number or currency format? It keeps merging as a number of varying decimal
points although it's formatting correctly in excel..

Add a formatting switch to the mergefield as follows:

{ MERGEFIELD ONLINEDATE \@ "MMMM dd, yyyy" }

Use Alt+F9 to toggle the display of fieldcodes on and off.

For more information on this subject, see "Formatting Word fields with
switches" on fellow MVP Graham Mayor's website at:

http://www.gmayor.com/formatting_word_fields.htm

--
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

"LennyBP" wrote in message
...
I am merging a date field from a .DBF database file. The field in the file
is
06/27/84. On my document I have the data file field as ONLINEDATE which is
the field name in the file.

What I would like to do is have it appear in my document as "June 27,
1984"
without the quotes.

If you can answer this, please be as detailed as possible.

TIA for your help. If possible, please also send the answer to




"macropod" wrote:

You can do that by adding a numeric picture switch to the mergefield. To
achieve a two-decimal currency result:
.. select the Word MERGEFIELD and press Shift-F9. You should see
something
like-
{MERGEFIELD MyData}
.. edit the Word MERGEFIELD to look like-
{MERGEFIELD MyData \# $,0.00} or {MERGEFIELD MyData \# £,0.00}, etc,
depending on the required currency format;
.. press F9;
.. run your mailmerge.

See Word's help file for more information on field formatting


Cheers


"registrarirene" wrote in
message
...
When mail merging from an excel file to a word document, how do I keep
the
number or currency format? It keeps merging as a number of varying
decimal
points although it's formatting correctly in excel..

I am trying to do the same formating to allow my field to show as currency
and entered the following (fiels is called CURBL)

{MERGEFIELD CURBL \ # $0,00}

When I merge it still shows as 30 instead of $30.00.



"macropod" wrote:

You can do that by adding a numeric picture switch to the mergefield. To
achieve a two-decimal currency result:
.. select the Word MERGEFIELD and press Shift-F9. You should see something
like-
{MERGEFIELD MyData}
.. edit the Word MERGEFIELD to look like-
{MERGEFIELD MyData \# $,0.00} or {MERGEFIELD MyData \# £,0.00}, etc,
depending on the required currency format;
.. press F9;
.. run your mailmerge.

See Word's help file for more information on field formatting


Cheers


"registrarirene" wrote in message
...
When mail merging from an excel file to a word document, how do I keep the
number or currency format? It keeps merging as a number of varying decimal
points although it's formatting correctly in excel..

Kate,
Try the following:
{MERGEFIELD CURBL \# "$0,00"}
The number (#) character should not have a space after the backward slash
(\), and the dollar sign ($) and numerals should be in quotes (" "), for the
switch.

--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) message rule
Previous Text Snipped to Save Bandwidth When Appropriate


"Kate" wrote in message
...
I am trying to do the same formating to allow my field to show as currency
and entered the following (fiels is called CURBL)

{MERGEFIELD CURBL \ # $0,00}

When I merge it still shows as 30 instead of $30.00.



"macropod" wrote:

You can do that by adding a numeric picture switch to the mergefield. To
achieve a two-decimal currency result:
.. select the Word MERGEFIELD and press Shift-F9. You should see something
like-
{MERGEFIELD MyData}
.. edit the Word MERGEFIELD to look like-
{MERGEFIELD MyData \# $,0.00} or {MERGEFIELD MyData \# £,0.00}, etc,
depending on the required currency format;
.. press F9;
.. run your mailmerge.

See Word's help file for more information on field formatting


Cheers


"registrarirene" wrote in
message
...
When mail merging from an excel file to a word document, how do I keep
the
number or currency format? It keeps merging as a number of varying
decimal
points although it's formatting correctly in excel..

The formatting switch should be

\# $,0.00

NOT

\# $0,00

In addition to the space between the \ and #, you have the comma in the
wrong place and you do not have the period.

--
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

"Kate" wrote in message
...
I am trying to do the same formating to allow my field to show as currency
and entered the following (fiels is called CURBL)

{MERGEFIELD CURBL \ # $0,00}

When I merge it still shows as 30 instead of $30.00.



"macropod" wrote:

You can do that by adding a numeric picture switch to the mergefield. To
achieve a two-decimal currency result:
.. select the Word MERGEFIELD and press Shift-F9. You should see
something
like-
{MERGEFIELD MyData}
.. edit the Word MERGEFIELD to look like-
{MERGEFIELD MyData \# $,0.00} or {MERGEFIELD MyData \# £,0.00}, etc,
depending on the required currency format;
.. press F9;
.. run your mailmerge.

See Word's help file for more information on field formatting


Cheers


"registrarirene" wrote in
message
...
When mail merging from an excel file to a word document, how do I keep
the
number or currency format? It keeps merging as a number of varying
decimal
points although it's formatting correctly in excel..

Hi Kate,

If you want just $, with no cents, then use:
{MERGEFIELD CURBL \# $,0}
otherwise:
{MERGEFIELD CURBL \# $,0.00}

Pay careful attention to the spacing.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

"Kate" wrote in message ...
I am trying to do the same formating to allow my field to show as currency
and entered the following (fiels is called CURBL)

{MERGEFIELD CURBL \ # $0,00}

When I merge it still shows as 30 instead of $30.00.



"macropod" wrote:

You can do that by adding a numeric picture switch to the mergefield. To
achieve a two-decimal currency result:
.. select the Word MERGEFIELD and press Shift-F9. You should see something
like-
{MERGEFIELD MyData}
.. edit the Word MERGEFIELD to look like-
{MERGEFIELD MyData \# $,0.00} or {MERGEFIELD MyData \# £,0.00}, etc,
depending on the required currency format;
.. press F9;
.. run your mailmerge.

See Word's help file for more information on field formatting


Cheers


"registrarirene" wrote in message
...
When mail merging from an excel file to a word document, how do I keep the
number or currency format? It keeps merging as a number of varying decimal
points although it's formatting correctly in excel..