Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Suppressing unwanted lines in Mail Merge documents

Posted on 2015-02-05
5
Medium Priority
?
95 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 23

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 23

Accepted Solution

by:
Ejgil Hedegaard earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

876 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