Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I stop 0's taking up lines in letter when no data in s/shee
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. |
#2
|
|||
|
|||
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" wrote in message ... 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. |
#3
|
|||
|
|||
Thanks so much! That worked very well.
"macropod" wrote: 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" wrote in message ... 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. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I stop having new typing delete the next letter? | Microsoft Word Help | |||
stop printing cell lines in excel docs linked to word | Microsoft Word Help | |||
Convert to Symbols | Microsoft Word Help | |||
How do I stop losing some address lines printing envelopes | Microsoft Word Help | |||
Date on letter changes to current date when opened - how to stop. | Microsoft Word Help |