Office & Productivity Software forum

General discussion

Mail Merge - Suppressing Zero's

by Khornight / June 11, 2007 12:57 AM PDT

I have a mail mearge from excel to Word and all the blank cells are coming through as 0's. How do I keep them blank or empty?

Preferably with field codes and work arounds, I don't have the ability to use VB at work.


Discussion is locked
You are posting a reply to: Mail Merge - Suppressing Zero's
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Mail Merge - Suppressing Zero's
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Try this formula
by MarkFlax Forum moderator / June 11, 2007 1:07 AM PDT

If you want the cell to show blank or empty, amend the current formula in the cell with this;

IF(A1=0,"",{Remaining formula})

A1 will be the reference cell from which the cell you are concerned with gets it 0.

So, if A1 =0, then fill this cell with an empty text string, (which is what the two quote marks do).

Otherwise, (then the remaining existing formula). Don't forget the final round bracket at the end of the IF statement.

Do that for each cell where this happens or may happen.


Collapse -
not a formula problem.
by Khornight / June 17, 2007 8:14 PM PDT
In reply to: Try this formula

The cells in excel are fine, in fact they don't contain formula at all, just data.

But when the data is merged into word the blank cells in excel are coming up as "0" in the word document.

I think I've found the reason, something to do with the mail merge seeing that most of the entries as numbers so pulling through blanks as zero, am going to try turning it all to text (ie cells contained "'

Collapse -
Mail-merge tip.
by Kees Bakker / June 18, 2007 12:19 AM PDT
In reply to: not a formula problem.

NEVER trust Word to format your data. ALWAYS use formatting in Excel. That's better tuned and much better checked.

Use function TEXT (and possibly IF to handle empty cells and exceptions) to format the data you want in your letter as text fields in a new column. Then merge them as text-fields (just like name and description and town) in Word.

A proven technique.


Collapse -
Thanks Kees,
by Khornight / June 18, 2007 3:51 AM PDT
In reply to: Mail-merge tip.

As always: informative and helpful.


Collapse -
More Mail merge questions...
by Khornight / June 26, 2007 1:40 AM PDT
In reply to: Mail-merge tip.

This must have come up before:
Maybe someone can suggest a good link?

If the word doc looks like this:
<<address line 1>>
<<address line 2>>
<<address line 3>>
<<Post Code>>

How do I stop there being a blank line between "address line 2" and the "post code" if there is a blank "address line 3"?


Collapse -
My suggestion.
by Kees Bakker / June 26, 2007 3:20 AM PDT

In your datasource define address_line_3 and address_line_4.
Use IF-statements (can be done easily both in Excel and in Access) to fill either
- address_line_3 with address line 3 and address_line_4 with post code (of line 3 is not blank), or
- address_line_3 with post code and address_line_4 with space (if line 3 is blank)

That's a good example of my maxim above: with mail merge, do everything in the datasource. One language (the Word mailmerge language) less to learn. IF's in Excel and Access are routine for me.

Hope this helps.


Collapse -
Excellent Idea...
by Khornight / June 26, 2007 4:05 AM PDT
In reply to: My suggestion.

I'm the same, Formula in excel I'm pretty good with, word I hate!


Collapse -
Now an excle question!
by Khornight / June 26, 2007 7:20 PM PDT
In reply to: Excellent Idea...

Ok, in my previous email I was simplifiying by using the address example.

It's actually a massive sheet with wages and bonus on, but I didn't want lines for bonus's when people didn't get them...

Now I have an excel sheet with columns (it was in rows but I transposed because I though it would be easier to do) in the format:
Fred Bloggs
Car Allowance

(only there are a lot more columns and rows)
(in this example the blank section contains Pension for other people)

How do I shift everything up to remove all the blank spaces?

Collapse -
Time to switch to MS Access maybe?
by Kees Bakker / June 26, 2007 7:37 PM PDT
In reply to: Now an excle question!

This seems to be on the difficult side for an Excel/Word combination. But it would involve a total rewrite of the application, of course.


Collapse -
Tell me why
by MarkFlax Forum moderator / June 26, 2007 9:23 PM PDT
In reply to: Now an excle question!

you need to shift everything up?

You seem to have;

Name ---- Basic ---- Bonus ---- Car All ---- Pension ---- Ins ----

Blogs ---- 25000 ---- 3000 ------- 3000 -------- Nil --- -------- 2000

Jones --- 20000 ---- 2000 ------- 1000 -------- 280 ----- -------- 2000

Is that right?

If so, is it not relevant that Blogs does not have any pension deductions?


Collapse -
Access scares me! :-)
by Khornight / June 26, 2007 11:25 PM PDT
In reply to: Tell me why

Keep saying here that I'll have to learn it, but now is not the time (maybe I'm just procratinating)

Ok to be more exact we pay people in different countries and so there are differnt types of insurance/pension/tax for each country.

This is for a letter that will go to employees, so not only would they be confused by seeing "Norweigian tax = 0", but also it would be bad form to remind everyone that doesn't get a car allowance that such a thing exists!!!

Have thought of appending each cell with the row number of the cell (which I can remove with easy later) and then sorting it but I can't think how to sort each column seperatly without selectiong them individually?!

Collapse -
Very ugly workaround.
by Kees Bakker / June 27, 2007 5:13 AM PDT
In reply to: Access scares me! :-)

1. Write an Excel macro to change 'all' empty cells to, say, 999,999.99. That's some 15 to 20 lines of VBA. Also write the reverse macro.
2. Apply the first macro and do the mail-merge to a Word document (not directly to the printer).
3. In the Word document record a macro to find 999,999.99 and then delete the whole line. Add some VBA-code to loop through the whole document.
4. After that, print the document.
5. Apply the reverse macro to the spreadsheet. Or restore the copy you made before messing it up. It might be handy to save the macro somewhere, just in case this is a repeating operation.

If we're talking about an internationally operating company, it seems they should be professionally enough to have or hire the necessary IT-expertise. Maybe that's you! Then start learning macro's and VBA.


Collapse -
Also, this being an international setup
by MarkFlax Forum moderator / June 27, 2007 5:18 AM PDT
In reply to: Very ugly workaround.

perhaps different spreadsheets, (in the same workbook), for the different nationalities. Then you can concentrate each sheet for the vagaries of the differing international wages/salary formulas.

I agree with Kees that this could get very messy without some rationalisation.


Collapse -
Even uglier work around!!!
by Khornight / June 29, 2007 1:29 AM PDT

I managed to do it with formula! first a form on a 2nd sheet that put the number of the row infront of the data (if not blank:=if(sheet1a1="","",Row()&sheet1a1), then another formula that took those numbers (left(a1,2)&left(a2,2)...) and a third (and final) sheet that used the numbers to pick out the right cells and place them without blanks!

As for IT, it was for data that IT weren't allowed to see! And lets just say they weren't climbing over each other to help.

In fact I wasn't allowed to see it either! I never got to see the sheet I was writing for!

As for it being an international company, the head office is in another country and I'm sure their IT department is very good, but ours here isn't that great (I've not been hired for my IT skills... actualyl that's not entirely true, but this post is long enough already!). It was also a time thing, our head office tend to take a month to reply, let alone help!

But I'm surprised this hasn't come up before, as I would have thought ordering data for a mail merge would be a relatively common question?

The differetn sheets wouldn't have helped either. Because people travel, so some have loads of different types of tax and they all relatively offset each other!

Thanks for all your help.

Collapse -
Continued from length limited thread.
by MarkFlax Forum moderator / June 29, 2007 9:36 PM PDT

I'm impressed with your solution. It's way beyond anything I would have attempted.

I'm pleased you got it sorted out m8.


Collapse -
As I said previously...
by Khornight / July 1, 2007 10:08 PM PDT

I'm pretty good with forumla! Happy

it's good too because sorting excel sheets with formula is something I've wanted to find a solution to before!

Popular Forums
Computer Newbies 10,686 discussions
Computer Help 54,365 discussions
Laptops 21,181 discussions
Networking & Wireless 16,313 discussions
Phones 17,137 discussions
Security 31,287 discussions
TVs & Home Theaters 22,101 discussions
Windows 7 8,164 discussions
Windows 10 2,657 discussions


Help, my PC with Windows 10 won't shut down properly

Since upgrading to Windows 10 my computer won't shut down properly. I use the menu button shutdown and the screen goes blank, but the system does not fully shut down. The only way to get it to shut down is to hold the physical power button down till it shuts down. Any suggestions?