Solved

Suppressing unwanted lines in Mail Merge documents

Posted on 2015-02-05
5
85 Views
Last Modified: 2016-02-11
How do I suppress an unwanted line in a mail merge when the data (a longish paragraph, derived from my linked Excel spreadsheet fields, which I build in the spreadsheet using CONCATENATE to construct words and numbers that makes up the whole paragraph, this paragraph being then a real cell-full in the spreadsheet!) - when this whole paragraph is not required in my merged Word document. If the paragraph is needed, then the spacing between the paragraphs is fine, but when I suppress this paragraph, I end up with one empty line too many.
0
Comment
Question by:BlosMusic
[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
  • 3
  • 2
5 Comments
 
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 40595618
If the cell in Excel is empty, or has an empty string, the line is not shown in Word.

Make the formula in the Excel sheet suppress spaces, so it ends with an empty string when not needed.
This require some if statements like this to concatenate A2, B2 and C2
=A2&IF(A2<>"",IF(B2<>""," "&B2,""),IF(B2<>"",B2,""))&IF(A2&B2<>"",IF(A2&B2&C2<>""," "&C2,""),IF(C2<>"",C2,""))
0
 

Author Comment

by:BlosMusic
ID: 40611805
Thanks. I'll try this, but my concatenations are really very large so I'm not sure how practical this will be.
0
 
LVL 22

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 40612375
Try this VBA function.

Function ConText(rg As Range) As String
    Dim i As Integer
    ConText = ""
    For i = 1 To rg.Cells.Count
        If Len(rg.Cells(1, i)) > 0 Then
            If Len(ConText) > 0 Then
                ConText = ConText & " "
            End If
            ConText = ConText & rg.Cells(1, i)
        End If
    Next i
End Function

Open in new window


The function reads the cells from left to right in the range, and inserts a space before the next cells content, if the cell is not empty.
If this is not what you need, please specify, as it is fairly easy to make the function do exactly what you want (when the rules are known).

Open the macro editor Alt+F11.
Select the Workbook in the project explorer in the left panel, press Ctrl+R if not visible.
Insert a module, menu Insert.
Insert the code.
Save the workbook with activated macros.

The function is used like any other function in Excel.
Type =ConText(
Mark the range with the cells to concatenate, and end the function with ), and Enter.
And it will be =ConText(A2:C2), for the cells A2:C2
0
 

Author Comment

by:BlosMusic
ID: 40631088
Sorry I have been a long time getting back to you. I have been working away.
I will try this. Thank you.
0
 

Author Closing Comment

by:BlosMusic
ID: 41358373
Once again, I have been bad at closing the question, but the answer was excellent. Many thanks.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
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…

751 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