How do I stop 0's taking up lines in letter when no data in s/shee

[Jess]

I have a list of data which contains names and numbers in an excel
spreadsheet. This is merged into a word document. The number of names
varies (up to 12). In my letter, it will display a 0 in some lines when
there is no name and number in the excel spreadsheet. This looks untidy and
takes up lines - how can I make it stop doing it?

I have formatted all columns in the spreadsheet to text. It is not always
the same column of numbers that is producing a 0 if there is no data, and it
is normally only 2 or 3 out of the 12 lines.

 

[macropod]

Hi Jess,

For a mailmerge field that imports numbers from Excel:
.. select the field
.. press Shift-F9 to reveal the coding, which should look something like:
{MAILMERGE ExcelVal}
.. add a numeric picture switch to this, thus:
{MAILMERGE ExcelVal \# 0;0;}

In place of the 0s above, you can also use strings like $,0.00 to show
results as currency, and so on. You can find more in Word's help file on
field formatting & picture switches.

For a mailmerge field that imports text strings from Excel:
.. select the field
.. copy the field
.. type 'IF' before the field
.. type '= "" "" ' after the field
.. paste the original field after the above.
You should now have a string looking something like:
IF«ExcelString»= "" "" «ExcelString»
.. select all of this string and press Ctrl-F9 to enclose it in a new field,
thus:
{IF«ExcelString»= "" "" «ExcelString»}
Note: If, after doing this and running the merge, you're still getting 0s or
superfluous spaces from a text-string field:
.. re-select the field
.. press Shift-F9 to reveal the coding, which should look something like:
{IF{MAILMERGE ExcelString}= "" "" {MAILMERGE ExcelString}}
and change it to:
{IF{MAILMERGE ExcelString}= 0 "" {MAILMERGE ExcelString}}
to supress 0s or:
{IF{MAILMERGE ExcelString}= " " "" {MAILMERGE ExcelString}}
to suppress superfluous spaces.


Cheers
PS: The character spacing in the above examples is important - leave any out
and you might not get the right results.

 

[Jess]

Thanks so much! That worked very well.

Hi Jess,

For a mailmerge field that imports numbers from Excel:
.. select the field
.. press Shift-F9 to reveal the coding, which should look something like:
{MAILMERGE ExcelVal}
.. add a numeric picture switch to this, thus:
{MAILMERGE ExcelVal \# 0;0;}

In place of the 0s above, you can also use strings like $,0.00 to show
results as currency, and so on. You can find more in Word's help file on
field formatting & picture switches.

For a mailmerge field that imports text strings from Excel:
.. select the field
.. copy the field
.. type 'IF' before the field
.. type '= "" "" ' after the field
.. paste the original field after the above.
You should now have a string looking something like:
IF«ExcelString»= "" "" «ExcelString»
.. select all of this string and press Ctrl-F9 to enclose it in a new field,
thus:
{IF«ExcelString»= "" "" «ExcelString»}
Note: If, after doing this and running the merge, you're still getting 0s or
superfluous spaces from a text-string field:
.. re-select the field
.. press Shift-F9 to reveal the coding, which should look something like:
{IF{MAILMERGE ExcelString}= "" "" {MAILMERGE ExcelString}}
and change it to:
{IF{MAILMERGE ExcelString}= 0 "" {MAILMERGE ExcelString}}
to supress 0s or:
{IF{MAILMERGE ExcelString}= " " "" {MAILMERGE ExcelString}}
to suppress superfluous spaces.


Cheers
PS: The character spacing in the above examples is important - leave any out
and you might not get the right results.