2

Is there a way to only show a comma in my data merged document if there is a second address value present?

The addresses in the CSV don't have commas to begin with and are entered as:
123 Main Street Suite A

I'd like them to print like:
123 Main Street, Suite A

If I simply add a comma between the merge tags (like <Address>, <Address 2>), entries without a second address display as:
123 Main Street,

What's the best way to hide the comma if there is no second value?

Improve this question
2
  • Does it really have to be written that way? Why not Suite A \n 123 MainStreet - then no commas are needed at all. \n = new line
    – Tetsujin
    Feb 11, 2021 at 18:59
  • @Tetsujin formatting it like that doesn't work with my design. I want it on one line
    – Manly
    Feb 18, 2021 at 14:45

2 Answers 2

Reset to default
1

Either

  • Move the comma into the second address column field of the source file (XLS, CSV, etc). This way, the comma only shows up where there is an actual second address field. This method however could be impractical if you have many entries.
  • Or, run a GREP replace after the import, and replace all commas at the end of a paragraph with nothing.
Improve this answer
7
  • My initial thought was to edit the csv file like this but there are a lot of entries and this is something I have to do bi-monthly, pulling the csv from one of our CMS databases, so it would be a pain. I've opted just to leave it without a comma since there doesn't appear to be a quick/easy solution
    – Manly
    Feb 17, 2021 at 19:41
  • @Manly If you’re pulling the data from a database, can you not just get both values into the same field separated by a comma when creating the file? Or do you not have the option of controlling the underlying DB call or the script (?) used to generate the file?
    – Janus Bahs Jacquet
    Feb 17, 2021 at 22:31
  • The GREP option is quite easy actually, like you find "comma end of paragraph" and replace with an "end of paragraph". This takes close to zero time regardless of how many entries you have.
    – Lucian
    Feb 17, 2021 at 22:59
  • @JanusBahsJacquet I don't actually pull the csv file myself and I'm fairly certain it's just as simple as someone else exporting it from our CMS. I don't think we have the control over scripting its export.
    – Manly
    Feb 18, 2021 at 14:41
  • 2
    @Manly In GREP an asterisk means ‘previous character zero or more times’, so , * means ‘comma followed by zero or more spaces’. However, assuming the line doesn’t contain anything else (i.e., there’s never anything after <Address 2>), the comma you’re targeting is at the end of the line (not necessarily paragraph, but line at least). You can search for ,\h*(?=\v) and replace with nothing; that pattern means ‘match a comma plus zero or more horizontal spaces (space, en space, tab, etc.), but only if followed a vertical space (line break, paragraph break, etc.)’.
    – Janus Bahs Jacquet
    Feb 18, 2021 at 17:44
0

You could edit your addresses in Excel before you do your data merge. This may not be helpful depending on your workflow.

I have your addresses in column A1, A2

Excel

I put the following formula in B1

=IF(RIGHT(A1,1)=",",LEFT(A1,LEN(A1)-1),A1)

Copy the formula for the remaining cells in column B. This will remove the comma if it is the last letter in the text string.

Final Example

Then delete column A and save as a CSV for the data merge.

Improve this answer
1
  • 1
    This process is one I need to repeat every two months on a csv with hundreds of rows. I thought about editing the csv, but I'm not the one entering the data initially and I don't feel like going through this everytime I pull the csv from our CMS. I decided just to leave it with no comma.
    – Manly
    Feb 17, 2021 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.