Avatar of nplanek
Flag for United States of America asked on

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"
Microsoft OfficeOffice ProductivityOffice Suites-Other

Avatar of undefined
Last Comment

8/22/2022 - Mon

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.

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

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.

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck

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.

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

Did you use the VBA code you referenced to remove the CR?  You may need to do the same for the LF character.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

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.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Solution provided by expert didn't work for me.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes