Mail merge showing 0 randomly

rexhvn

New Member
Joined
Jan 7, 2016
Messages
27
Hi guys,

Im after some assistance please. This is the first time this has happened. I have a mail merge which displays invoice numbers which are all numeric. I use a formula that vlookups them up only if they exist and will return "" if not, until today it was working fine but now i get random 0 as values.

Example, ill have invoice 1 and invoice 2. Lets say there are no invoice numbers for invoice 2 therefore my cells in excel are blank. When i click through and check the data in word after ive inserted the field say there are 10 entries for invoice 2, some have a 0 value? Say 3 have 0 and the rest are blank?

I dont understand why some are showing 0 and some arent when the formula is the same throughout and should be just blank?

Can anyone share any light?
Thanks
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
The reason for this is that your invoice #s are numeric, so mailmerge treats the entire column as numeric. Empty cells are thus equivalent to 0. If you just need to hide such #s, select the Invoice mergefield, press Shift-F9 to expose the field code, which will look like:
{MERGEFIELD Invoice}
then add a numeric picture switch to the it along the lines of:
{MERGEFIELD Invoice \# "0;;"}
Press F9 to update the field, then run the merge.

For more on mailmerge data formatting, see the Mailmerge Tips and Tricks threads at:
Mailmerge Tips & Tricks
or:
Word Mailmerge Tips & Tricks | Windows Secrets Lounge
 
Upvote 0
The reason for this is that your invoice #s are numeric, so mailmerge treats the entire column as numeric. Empty cells are thus equivalent to 0. If you just need to hide such #s, select the Invoice mergefield, press Shift-F9 to expose the field code, which will look like:
{MERGEFIELD Invoice}
then add a numeric picture switch to the it along the lines of:
{MERGEFIELD Invoice \# "0;;"}
Press F9 to update the field, then run the merge.

For more on mailmerge data formatting, see the Mailmerge Tips and Tricks threads at:
Mailmerge Tips & Tricks
or:
Word Mailmerge Tips & Tricks | Windows Secrets Lounge

If thats the case shouldnt all my blanks then show 0 and not just a random few?
 
Upvote 0
Without seeing precisely what the data in your Invoice columns are, I can't say why you might get different behaviours. Regardless, I though the aim was to solve the problem, not debate the cause.
 
Upvote 0
Without seeing precisely what the data in your Invoice columns are, I can't say why you might get different behaviours. Regardless, I though the aim was to solve the problem, not debate the cause.

Sorry just trying to understand why it occured so i can learn.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top