Mail merge showing 0 randomly

rexhvn

New Member
Joined
Jan 7, 2016
Messages
19
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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,559
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
 

rexhvn

New Member
Joined
Jan 7, 2016
Messages
19
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?
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,559
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.
 

rexhvn

New Member
Joined
Jan 7, 2016
Messages
19
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,307
Messages
5,641,442
Members
417,209
Latest member
Agbarker

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
Top