Conditional Currency/Text Field Format in Merge

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Suzamo - 13 Mar 2007 20:22 GMT

I have a field in an Excel spreadsheet that can be either currency or text.
I mail-merge it into a Word document. The field contains donation info,
which may be things like "$25.00" or "4 tickets to ABC Theater".

When I add the switch to get the currency to format correctly, it messes up
the format when the field contains text. If I get the $25.00 to format
correctly after the merge, "4 tickets to ABC Theater" looks like "$4.00".
Without the switch, the "4 tickets to ABC Theater" looks good, but the
"25.00" is "25".

Any suggestions? Thanks!

Reply to this Message

Peter Jamieson - 13 Mar 2007 20:34 GMT

Broadly speaking, Word does not work well with a mixture of numbers and
text in the same column in Excel. See

http://tips.pjmsn.me.uk/t0003.htm

for an explanation and some suggestions: in this case, using a DDE
connection is the only approach that is likely to work without much more
effort.

Peter Jamieson

>I have a field in an Excel spreadsheet that can be either currency or text.
> I mail-merge it into a Word document. The field contains donation info,
[quoted text clipped - 8 lines]
>
> Any suggestions? Thanks!

Reply to this Message

macropod - 14 Mar 2007 11:07 GMT

Hi Suzamo,

You could code around it with:
{IF "{MERGEFIELD Data}" = "{={MERGEFIELD Data}}" "{MERGEFIELD Data \# $,0.00}" "{MERGEFIELD Data}"}
but if you've got a lot of different fields like this, using a DDE connection would be better, as Peter suggests.

Cheers

Signature

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

>I have a field in an Excel spreadsheet that can be either currency or text.
> I mail-merge it into a Word document. The field contains donation info,
[quoted text clipped - 7 lines]
>
> Any suggestions? Thanks!

Reply to this Message

Peter Jamieson - 14 Mar 2007 12:06 GMT

Fine as long as your data does not change so that the first 8 rows (or the
number of rows specified in TypeGuessRows the registry) in the column do not
all happen to have numeric data, at which point all the the text values in
the column suddenly start appearing as 0.

That;'s the trouble with this stuff - from a user persective, insert one row
in your data and all of a sudden the behaviour of your merge document
changes quite radically for no apparent reason.

Peter Jamieson

> Hi Suzamo,
>
[quoted text clipped - 14 lines]
>> "$4.00". Without the switch, the "4 tickets to ABC Theater" looks good,
>> but the "25.00" is "25". Any suggestions? Thanks!

Reply to this Message