Data Merges: if variable isn't blank, then insert comma and space

[sherimartin]

In my work I have a customer who needs a complex data merge for a recurring mailing. They gave us a spreadsheet of the mailing recipients, their addresses, and also three variables (Color 1, Color 2, Color 3). We need to merge the data onto envelopes so that the color variables are separated with commas like this:

Color 1, Color 2, Color 3

It gets complicated because not all of the recipients have data in all three color fields. A good number only have two of the fields filled out, and others only have one filled out. If I set up the InDesign file like this:

<<Color_1>>, <<Color_2>>, <<Color_3>>

then extra commas will appear where they shouldn't in instances that only one or two color variables are used.

Right now it looks like my only options are to either manually format these spreadsheets, which is prohibitively time consuming, or find a way to automate the insertion of commas ONLY if they're needed. Does anybody know if InDesign is capable of inserting commas between the variables? Any way I could insert some javascript that will run during the merge that will insert them? Or is there a way to automatically insert commas in the spreadsheet but ignore empty cells?

[SJRiegel]

I saw a similar question earlier today - maybe the solution presented there will work for you, too.

Re: Complex In Design Mail merge

[sherimartin]

It does help, thanks!

[vinny38 · Accepted solution]

Hi there.

It's not only about commas here, but also spaces right?

I would wave my Grep magic wand on this one...

Assuming your 3 colors date fields are on a single line (paragraph):

Hocus-pocus:

- hide commas and spaces when starting paragraphs

- hide commas and spaces when ending paragraphs

- hide consecutive commas and spaces when followed by a comma and a space

Would translate like this (you can replace \h by regular spaces if you prefer):

^(,\h)+|(,\h)+$|,\h\K(,\h)+

Now, create an invisible character style:

- font size: none

- tracking: -1000

- H&V scale: 1%

- Color: [none]

Finally, use Grep style to apply this "invisible" character style to the regex above and let the magic happen...

See illustration below:

Hope that helps

Vinny

[sherimartin]

This is exactly what I was looking for! Thanks so much for the help.

[Stephen_A_Marsh]

It should not take a lot of work to reformat the spreadsheet data... However, I have not seen your spreadsheet. It does not really matter if you have a large or small amount of columns or rows.

I played around using filters on the spreadsheet columns to include/exclude blank or colour text, but was not happy.

So I then settled on the following:

1. Add a new blank column

2. Use the concactenate formula to string together the three colours into the new column

3. Apply the formula to all rows in this new column to auto populate the results

4. Copy and paste as values over the top of the formula result so that the values can be edited

5. Use find/replace to replace with the commas where necessary in the new column

Should take less than a minute once you know what to do. These days I spend a lot of time importing/exporting data into Print MIS databases, ETL etc. Lots of fun with formulas and regular expressions/grep etc.

Animated screenshot example:

[sherimartin]

A good idea for a low number of color choices, but unfortunately, my list is a bit more complicated. We're mailing out samples of a product where there are a large selection of colors, and the recipient of the samples can choose up to three of any of them, in any combination and order. That would be a lot of finding and replacing.

[Stephen_A_Marsh]

That would be a lot of finding and replacing.

Perhaps using a simple spreadsheet find/replace, however, if the data was taken into a regular expression based text editor it could be handled by one ugly-complex find/replace. There are many possibilities for manipulating source data.

P.S. Don't get me wrong, I love vinny38's answer, I have suggested similar approaches to such problems in the past.

[vinny38]

Hi Stephen.

Although I personally prefer a Grep solution that doesn't involve modifying the spreadsheet, I agree it is a interesting alternative.

But, I think you can avoid the find/change process using a formula:

NB:

=SUBSTITUE(SUPPRESPACE(CONCATENER(A4;" ";B4;" ";C4;" ";D4));" ";", ") is a french formula

give =SUBSTITUTE(TRIM(CONCATENATE(A4," ",B4," ",C4," ",D4))," ",", ") a try (Traducteur • Excel-Translator )

[Stephen_A_Marsh]

That's cool, thanks, I have not strung those 3 particular formulas together before!