Copying an excel multi line cell into an Indesign table

Question

I'm creating a booklet with indesign and I am trying to copy data from an excel spreadsheet into an indesign table. I was given an excel spread sheet with the a list of peoples names, their titles and their companies. I have to put a name in one column and then in the other put the company name and their title, as the picture (1) here illustrates:

Where the problem arises is trying to merge two cells in excel so that they are underneath each other and then paste it into one cell in the indesign table.

I tried to combine column C and D (as IN THE PICTURE) and insert a new line between the columns. (=C1&CHAR(10)&D1)

This worked in Excel but when copying it into indesign they do not go underneath each other but into different cells and they also show " quotation marks around the text.

I know there must be a relatively simple way of doing this in Excel and then copying it into indesign but I have run out of ideas. Can anyone tell me the best way to approach this as I am stuck. Thanks in advance.

Answer 1

Never use any new line characters in your Excel data. When InDesign comes across a new line character it will begin a new row, and not a new line within the same cell.

You're on the right track concatenating the two columns into one in Excel, but, as Lauren Ipsum suggests, use a placeholder (eg. a tilde '~') instead of the CHAR(10).

Then, copy your Excel data and paste it into an InDesign table. Then Find/Change each tilde with a new line character and you're done.