Solved

Suppressing unwanted lines in Mail Merge documents

Posted on 2015-02-05
5
67 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
  • 3
  • 2
5 Comments
 
LVL 21

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 21

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Lately there has been a variety of news related to U.S. employment.  Stories about worker productivity, automobile and airline unions, low employment and foreign laborers have frequented the news.  Each story has good and bad attributes we might arg…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

757 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

20 Experts available now in Live!

Get 1:1 Help Now