Convert blank field or "0" to a text

[Yogi]

Hi there,

I am writing a large spreadsheet thet needs to be merged in a stock
reporting form that goes to approximate 100+ clients. When there is no value
in a spreadsheet cell or is a -0- I need mailmerge to convert it to text "Not
Available" I have searched through forums but can't find a solution, can any
one help?

Thanks.

 

[Peter Jamieson]

IN those circumstances you should be able to use a nested field such as

{ IF "{ MERGEFIELD mynumberfield }" = "" "Not available"
"{ IF { MERGEFIELD mynumberfield } = 0 "Not available" "{ MERGEFIELD
mynumberfield }" }" }

where every pair of {} are the special field code braces you can insert
using ctrl-F9.
However, it does depend on exactly what is contained in your Excel sheet and
the tests you need to make in Word need to be based on what appears when you
insert the simple field

{ MERGEFIELD mynumberfield }

(where "mynumberfield" is the name of your Excel field/column)

 

[macropod]

Hi Yogi, you could add a numeric picture switch to your mergefield. To do this:
.. selected your mergefield and press Shift-F9. You should see something like '{MERGEFIELD Quantity}'
.. edit the mergefield to look like '{MERGEFIELD Quantity\# "0;;Not Available"}
.. press F9 to update the mergefield
.. run your mailmerge

 

[Yogi]

Ok so I have to explain my issue in more detail.

Other than converting -0- to a text I am also formatting the numbers to a
comma separated format. Currently what I am using is
\#,###.00
to convert the data from the spreadsheet to comma separated and in the same
column there are several cells that have a -0-, I need to make sure that
imported numbers are correctly formated and if a -0- apprears, merge should
say "Not available" instead of showing a -0-.

 

[Doug Robbins - Word MVP]

Apply the following formatting switch to the mergefield:

\# "#,###.00; ;Not Available"

If there is a format that you need for negative numbers, insert it between
the two semicolons.

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

 

[macropod]

hi Yogi,

You could edit the mergefield to look like '{MERGEFIELD Quantity\# ",0.00;-,0.00;Not Available"}
but do note that regarless of whether your data contains '0' or nothing, both evaluate to '0' and will trigger the 'Not Available'
result.