Solved

Suppressing unwanted lines in Mail Merge documents

Posted on 2015-02-05
5
80 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
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:
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 …

740 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