I find that the Data Merge tool is one of the most under-rated features of InDesign and most tutorials which one can Google on the Internet relate to either making business cards, old-fashioned mail-merges a la Microsoft Word, or rather rigid-looking catalogues.

However, once the Data Merge tool is combined with either clever spreadsheets using Microsoft Excel, GREP styles, anchored objects and applying features from the Effects palette or Object Effects, the Data Merge tool becomes much more powerful.

In previous posts I’ve done exactly this to create:

• Pie Graphs
• Bar Graphs
• A one-size fits all name-badge solution

In this post, I will demonstrate how to make more interesting campaigns using Data Merge. Examples include:

• Street Sign
• Speech Bubble
• Pencil Case letters
• Scrabble Letters

In all of the following examples, the database names:

• were between 3-11 letters long;
• were English letters (no letters with accents, umlauts, cedillas, etc). Examples 3 and 4 are possible with foreign letters but would involve more steps not shown in this post.
• were whole names (no spaces or dashes). Spaces and dashes are possible but in examples 3 and 4 would involve more steps not shown in this post.

1: STREET SIGN

The easiest of the four examples, the principle for this merge works using 3 things

• a blue background with a blank street sign running along the entire page;
• a centered text block above the street sign;
• two anchored object images: the pole plus some blue sky; and blue sky tapered on an angle.

Put simply, the centered textbox contains the mergefield plus the word “ST”. This sits above the street sign and one anchored object (the pole plus the blue sky strip) goes to the left of the mergefield, and the other anchored object of tapered blue sky goes to the right of the mergefield.

The result is as the name changes, so does the position of not only the pole, but where the street sign ends, resulting in a dynamically made street sign which is always centered in the sheet.

Download the Street Sign PDF (which contains the InDesign file as an attachment).

2: SPEECH BUBBLE

This works on a similar principle as the street sign, but because anchored objects always go in front of the textbox they are pasted into, two textboxes have to be made: one containing the variable data (in white) plus the parts of the speech bubble which have to expand; and another containing the variable data only in black in the textbox above.

All works well with this design until the merge comes to the record of Christopher, where gaps appear between the plain rectangle and left/right of the bubble. To compensate for this, a GREP style was made to the type in both textboxes so that once there were 11 characters, the character style became scaled. This is much like the trick used in a recent post, the Square Peg in a Round Hole.

Download the speech bubble PDF (which contains the InDesign file as an attachment).

3: PENCIL CASE LETTERS

This was MUCH HARDER given that:

• The letters were not fixed width, so each “letter” had to be in its own merge; and
• Pencil case letters do not usually have blanks all the way to the end of the plastic inserts.
• The letters are right aligned

To do this one, the first thing was to make the letters A-Z. This was done by making an InDesign file 26pp long with the design on the master page containing both the background and the letter as alpha page numbers. This was then made into PDFs which were split into single pages and renamed A.pdf through to Z.pdf using Batch rename in Bridge.

The next trick was getting the names to appear in the database as single letters and in such a fashion that the letters would appear in the correct places. This meant making a formula which would:

• Know which letter was in a certain position (e.g. 3 letters in, at the end, etc);
• Know if a letter had nothing in it and would remain blank;
• Would add the letters .pdf at the end of the result so that the merge would know it was looking for a picture, rather than placing text.

So the following formula was made for all letters except for the last letter:

=UPPER(IF(ISTEXT(MID($A2,LEN($A2)-X,1)),MID($A2,LEN($A2)-X,1)&”.pdf”,))

where X represents how many letters away from the right hand side that needs to be displayed in the cell, and A2 is the name which is to be split into individual letters.

The last letter had the following formula:

=UPPER(IF(ISTEXT(MID($A2,LEN($A2)*1,1)),MID($A2,LEN($A2)*1,1)&”.pdf”,))

The excel file was saved just in case, but was also saved to a Tab Delimited Text file for InDesign.

The rest was easy. Pencil case clipart is to the back, plastic holders are to the front, and 11 graphic frames representing the letters are made. Once the data is called in from the Data Merge palette, the picture fields were put into the appropriate graphic frames so that, once preview was on, the letters would change and but effectively be right-aligned, and contain necessary blank squares.

Download the Pencil Case PDF (which contains the InDesign file, pictures and excel files as attachments).

4: SCRABBLE LETTERS

This was harder than the pencil case letters but employed many of its techniques such as:

• Requiring images to be made of the letters rather than using type-based letters
• Using a similar excel spreadsheet to break the names into 11 separate PDFs, with the only exception being that if a field was blank, it was to reference a picture called BLANK.PDF

The easiest part was the background which was just clip-art. The letters were made in a similar fashion to the last post, except that the letters deliberately had some space to the left and right of the letter. This will become apparent soon why this was done.

The excel file was essentially the same except for the addition of the following letters at the end of the formulas:

=UPPER(IF(ISTEXT(MID($A2,LEN($A2)-X,1)),MID($A2,LEN($A2)-X,1)&”.pdf”,“BLANK.PDF“))
=UPPER(IF(ISTEXT(MID($A2,LEN($A2)*1,1)),MID($A2,LEN($A2)*1,1)&”.pdf”,“BLANK.PDF”))

The addition of this type returns the result “BLANK.PDF” anytime there is no letter to show. This will be important.

Next, a centered textbox is placed above the clipart. A rectangle the size of the scrabble letters is then drawn, cut, and then pasted into the centered textbox as inline text – eleven times. From here, the data is called in via the Data Merge palette and the fields are dragged into position, but BEFORE preview is checked, there is a very important option which has to be selected in the Content Placement Options, namely that in Image Placement (fitting) that the option “Fit Frames to Images” is selected.

It is also important that before hitting preview, that PDFs imported into the art use the Art measurement. To make sure that this happens, import any PDF with the “show import options” on. Once the import options dialog box appears, make sure that the art measurement is used. Click ok, but then delete the picture. This should ensure that all future PDFs placed into the art use the Art box as the defining measurement.

From here, if Preview is checked, the names should now appear centered. Without the “Fit Frames to Images” option being checked, the letters would appear off-centre because of the blank rectangles to the left.

The final trick so that the letters appear to be sunken into the board was to draw a rectangle (no stroke or fill) in the area where the letters should be in the rack, and then cut the centered letters and “Paste Into” the rectangle which was just drawn.

Download the Scrabble Letters PDF (which contains the InDesign file, pictures and excel files as attachments).