Conditional Currency/Text Field Format in Merge

[Suzamo]

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!

 

[Peter Jamieson]

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

 

[macropod]

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

 

[Peter Jamieson]

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,

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