Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Convert blank field or "0" to a text
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. |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Convert blank field or "0" to a text
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) -- Peter Jamieson http://tips.pjmsn.me.uk "Yogi" wrote in message ... 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. |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Convert blank field or "0" to a text
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 -- Cheers macropod [MVP - Microsoft Word] "Yogi" wrote in message ... 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. |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Convert blank field or "0" to a text
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-. |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Convert blank field or "0" to a text
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 "Yogi" wrote in message ... 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-. |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Convert blank field or "0" to a text
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. -- Cheers macropod [MVP - Microsoft Word] "Yogi" wrote in message ... 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-. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert {xe "..."} index entries to text that I can copy | Microsoft Word Help | |||
How to convert "normal" text to floating left and right justified text? | New Users | |||
How can I create a "blank-if-empty" numeric input field in Word 20 | Microsoft Word Help | |||
accidentally saved text into the "new blank document". | Microsoft Word Help |