Mail Merge Problems

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"
nplanekOffice ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
nplanekOffice ManagerAuthor Commented:
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.
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

nplanekOffice ManagerAuthor Commented:
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.
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.
nplanekOffice ManagerAuthor Commented:
Example of Excel column data:

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?
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.
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.
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

larryhSr. Software EngineerCommented:
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.
nplanekOffice ManagerAuthor Commented:
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 "".
larryhSr. Software EngineerCommented:
Did you use the VBA code you referenced to remove the CR?  You may need to do the same for the LF character.
nplanekOffice ManagerAuthor Commented:
It's putting the return in data that's in one cell.
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.
nplanekOffice ManagerAuthor Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nplanekOffice ManagerAuthor Commented:
Solution provided by expert didn't work for me.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.