How to auto delete a space left after a data merge in InDesign?

Question

I have an InDesign document which is filled from the data in a CSV file using data merge. The CSV has columns for First Name, Middle Name & Last Name, but some of the data in the CSV isn't filled in - such as Middle Name if the person doesn't have one. InDesign then leaves a physical space when the middle name is missing which is putting a double space between the first and last name when I merge the data. Is there any way to get InDesign to ignore blank cells in the CSV file?

Answer 1

Are you sure those spaces exist in your CSV file? They shouldn't.
Did you not manually added the spaces between the fields?
Like this: (dots represent spaces)

<<Firstname>>.<<Middlename>>.<<Lastname>>

It does make sense since you want a space between fields.
A solution (among many possible ones) is to use a grep style to hide a space if followed by another space. Something like this should do the trick:

\h(?=\h)

To create an invisible character style, you can use these settings ("Invisible character style" is just like pancakes: everyone has his own recipe... here's mine ^ ^):
color : [none]
size: 0.1 pt
Tracking: -1000
horizontal scale: 1%

See example below:

Answer 2

You can also merge columns in your excel file and put formula to remove unwanted spacing

=TRIM(A2&" "&B2&" "&C2)

Where A2 is name, B2 is Middle Name and C2 is Surname

Formula for only 2 columns to merge would be =TRIM(A2&" "&B2)

Answer 3

This solution affected the entire text, not just my spaces. I feel this also uses a complicated/messy solution by inserting 'extreme' values to remove the spaces. The better option may be the following:

\h{2,}

I then used -78 for the Tracking on the Character Style to adjust for the extra space on a 10pt font. (This was the only option that I needed to adjust, simple).

You will have to do your own math to figure out the correct negative tracking value for your font size. Hope this helps someone because the original solution did not work for me. It is possible that with InDesign 18.1 (2023) that GREP function \h(?=\h) no longer works as it used to. (Also possible that I am doing something wrong, but I prefer this cleaner method).

Answer 4

In the Data Merge menu, choose "Content Placement Options", and select "Remove Blank Lines for Empty Fields", then click "OK" - it should work.