Solved

Mail Merge Problems

Posted on 2014-12-01
16
176 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
  • 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
 

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now