Solved

Mail Merge Problems

Posted on 2014-12-01
16
189 Views
Last Modified: 2014-12-07
I'm using Word 2013 to create envelopes with information from an Excel spreadsheet.  Two questions,
(1) If a cell is empty what can I do so Word doesn't insert a blank space.
(2) How do I get rid of the "" marks in the following example (I think that's the reason it puts the suite number on a different line).  The quotations are NOT visible in the cell and can not be found:
 
"100 S. Wacker Drive
, Suite 920"
0
Comment
Question by:nplanek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 2
16 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40474385
What do you want to appear instead of a space?
I think that we will need to see (the relevant part of) the merge main document in order to see where the spurious quotes might be coming from.
0
 

Author Comment

by:nplanek
ID: 40474419
I want the space eliminated so if there's a PREFIX column that's empty and a FIRST_NAME column, FIRST_NAME would be the only thing that prints.

The quotes are in the cell of the EXCEL document.  I've tried changing the formatting and copying and pasting the formatting of a cell that prints correctly....didn't work.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40474438
So it is the word 'PREFIX' that you don't want to see in those circumstances?

If the quotes are in the datasource, there is no simple way of removing them as part of the merge process.
0
Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

 

Author Comment

by:nplanek
ID: 40474458
PREFIX is the column heading, so if a cell under PREFIX is empty nothing should be there.

I don't want to remove them as part of the merge process, I just want to get rid of them in the Excel cell.  I just retyped the cell and the quote marks disappeared, but there's numerous instances showing quotes so I'd rather not have to go through the entire worksheet to manually correct them.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40474529
Still not sure that I understand  correctly. If you have some fixed text that you only want to see if the corresponding field is not blank, then you would need some fields that look like this when field codes are displayed (Alt + F9).
{ IF { MERGEFIELD Prefix } = "" "PREFIX {MERGEFIELD Prefix}" }

On the other hand, you seem to be saying that you don't want a blank printed when the data is blank. Is some extra space printed when the cell is blank? Otherwise a blank is just where nothing is printed.

Re the quotes - this is also somewhat confusing. Are the quotes in the worksheet or not?  Perhaps it is a sample workbook that we need to see.
0
 

Author Comment

by:nplanek
ID: 40474596
Example of Excel column data:

PREFIX           FIRST NAME       LAST NAME
Mr.                   Jeffery                      Chamberlin
Alderman    Pat                      Dowell
Mr.                   Robert               Charles
Alderman    Michelle             Harris
                   Loren                 Harris

Examples of merged Word data:

Mr. Jeffery Chamberlin
Alderman Pat Dowll
Mr. Robert Charles
Alderman Michelle Harris
 Loren Harris (What do I need to do to get rid of the space before Loren)

See attachments for the second question.  Why does the second address move the suite number to another line?
Christmas-Cards---Test.xls
Envelopes---Test.doc
Envelopes.doc
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40474866
Currently, the space is part of your document, so, in principle, we must do the same as in my previous answer, except that it is a space rather than the word 'Prefix'.

To see Word field codes you need to use the Alt+F9 toggle. This switches between the 'results' and the code view.
So what your see will go from this:
«Prefix» «First_Name» <<...

to looking like this
{ MERGEFIELD Prefix }  {MERGEFIELD First Name} {

You now need to work on the first field. Copy and paste it so that there are two copies of the field side-by-side. Select the two and use Ctrl+F9 to enclose them both it a new pair of field delimiters. Edit the text so they are contained in an IF filed and look like this.
{ IF { MERGEFIELD Prefix } = "" " { MERGEFIELD Prefix}" }{

Note that the space between the second copy of the Prefix field and the following First Name field has been removed, but there is now a space just before the second copy of the field - following the quotes. Here it, and the text of the Prefix won't be printed if the Prefix field is blank. Also note that the curly brackets can't be typed in one-at-a-time. Field delimiters look a bit like the keyboard characters, but are different.

You can use Alt+F9 to switch back to the results view, but, since the IF field can't be evaluated, you will no longer be able to see the Prefix Mergefield.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40474873
Still working on the address line folding part of your question. It's getting late here, so I may have to come back to it in the morning.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40475474
The folding is caused by an invisible carriage return character in the text. I'm more Word than Excel, so I don't know what the recommended method for finding and replacing non-printable characters in a worksheet is, but this VBA macro does the job.
Sub ReplaceCarriageReturns()
    Dim cl As Range
    For Each cl In ActiveSheet.UsedRange
        cl.Value = Replace(cl.Value, vbCr, "")
    Next cl
End Sub

Open in new window

0
 
LVL 2

Expert Comment

by:larryh
ID: 40476195
Do you have any control of the spreadsheet?  If it were me, I'd create a cell in the spreadsheet that was exactly what I wanted to print out on the envelope.  A column titled "Line1" for the first line, with or without the prefix, a column titled "Line2" for the second line, etc.  That way the Word file doesn't need any special handling.  

That's my .02.
0
 

Author Comment

by:nplanek
ID: 40476206
Graham...I tried your solution but it removed all the Prefix information.  I did find the following solution on line which worked:

The key here is to make the space after each merge field's data part of the field. It's not difficult. First delete the existing spaces between the fields so they're continuous, something like this: { MERGEFIELD Prefix }{ MERGEFIELD First }{ MERGEFIELD Middle }{ MERGEFIELD Last }{ MERGEFIELD Suffix }. Right-click the first field and choose Edit Field from the context menu. Check the box labeled "Text to be inserted after", type a space in the text field next to it, and click OK. Repeat for the other fields. When done your set of fields will look like this: { MERGEFIELD Prefix \f " " }{ MERGEFIELD First \f " " }{ MERGEFIELD Middle \f " " }{ MERGEFIELD Last \f " " }{ MERGEFIELD Suffix \f " " }. You can also edit the fields directly to add the text \f " "

I'm still having the problem with some of the addresses being split; it wasn't because of the invisible "".
0
 
LVL 2

Expert Comment

by:larryh
ID: 40476221
Did you use the VBA code you referenced to remove the CR?  You may need to do the same for the LF character.
0
 

Author Comment

by:nplanek
ID: 40476228
It's putting the return in data that's in one cell.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40476670
The logic of what I suggested is that if  the Prefix field is blank, output nothing, otherwise output a space, followed by the text of the prefix field. I don't know why you get something different, but if you are willing to modify the datasource then your method should work as well.

I'm a bad guesser, so I also don't know what is in any other cells that produce split lines.
0
 

Accepted Solution

by:
nplanek earned 0 total points
ID: 40477011
A space followed by the text of the Prefix field is not what I needed; space after the prefix field is.  No matter, that problem is  solved.

As far as the second issue...I retyped the cells that split inappropriately...problem solved.
0
 

Author Closing Comment

by:nplanek
ID: 40485327
Solution provided by expert didn't work for me.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question