Excel 'named range' text import with paragraph returns

[steveg78558378]

Hi

I've used Data Merge extensively in the past, I've also imported/placed text using 'Create links when Placing Text and Spreadsheet Files' for live data/text updating. I've been using WordsFlow (Em Software) for 12 months which is a really handy tool. So I've a reasonable amount of experience dealing with importing Excel files.

But.... does anyone know if it is possible to import a named range (just a list of text items from one cell) from Excel into InDesign in such a way that the list appears separated by paragraph returns. I realise that a named range has to contain more than one cell - but i've found that if I name the range of two cells and then merge them into one it works fine.

I can only get the text to appear with 'soft returns/forced line breaks' but I need them to be paragraph returns - formatted by cell/paragraph styles to appear as a list of bullet points.

I really need the file to update independently - without having to run a find/change to achieve this.

MTIA

Steve

[Francesco Della Peruta]

If I well understand the problem, maybe, instead of using named range in this way, you could use some function to generate the string, separated by paragraph return, in excel, like concatenate()...

https://trumpexcel.com/concatenate-excel-ranges/

[vinny38]

Hi there

FYI, there's another method to import single cells named ranges. See here:Re: excel-indesign

Now, about your main query, I'm afraid there's no fully automated way to achieve what you want.

You need some manual intervention at some stage.

My first answer would have been "use F/R", but you don't want it...

So, my second answer would be: create a new column in Excel and use =SUBSTITUE formula to turn forced line breaks (CAR(10) in Excel) into regular carriage returns (CAR(13))

Hope that helps

Vinny

Edit : oh, of course I use a French Excel, so change the formulas accordingly to your Excel version.

=SUBSTITUTE(yourOrginalNamedRange;CHAR(10);CHAR(13)) maybe?

[Laubender]

Hi Vinny,

thank you very much for this example.

First I struggeled with my German Excel, but now it's working like that:

=WECHSELN(A1;ZEICHEN(10);ZEICHEN(13))

Do you know a table where the different function components are listed with their locale terms?

English, French, German etc.pp. in comparison so if I find a French function I could translate that to my German Excel?

Five minutes later: Ah. Found this:

https://www.excel-function-translation.com/index.php?page=francais-english.html

https://www.excel-function-translation.com/index.php?page=deutsch-english.html

So I would look up the French term first to get the English one and then compare the English one in the German-English list.

Regards,
Uwe

[Laubender]

Also found this:

Alle • Excel-Translator

Regards,
Uwe

[steveg78558378]

Thanks guys, two good alternatives, and I've learned some new stuff.

I'm trying to eliminate manual handling in the files.

I was asking the question in an attempt to automatically fill a text box in Indesign (set to split across two even depth columns, text from linked excel file/database) .

I think I will opt to import the data as a series of table cells, set the first column to a set depth of x9 table cells with remainder of text in second column (in Excel).

Once the database is set up this way any additions/deletions from the list will update accordingly

The only downside to this solution would be if the list grew by more than another six items, which could have been catered for by adjusting the paragraph style or having adjustable text box depth the other way and synchronised across all files.

.

Many thanks!

Steve