Solved

Suppressing unwanted lines in Mail Merge documents

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create a Dynamic Dropdown List in Excel excluding Blanks 5 35
Update Match Entries 4 20
Automating Excel Weekly Report 13 63
send keys not working in vba 7 41
As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

867 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

16 Experts available now in Live!

Get 1:1 Help Now