Coping the content of a range of cells in only one cell

English
#1

Hello Everyone,

I am trying to put the content of a range of cells in only one cell. Specifically the content is a list of names :
In my Sheet 1, I am adding a formula that is grouping all the names that are on the Sheet 2, Column A. In the Sheet 2, Column A, I have the following :
A2: Marlon Brando
A3: Marilyn Monroe
A4: Winona Ryder;
etc…
What I want in my Sheet 1, is retrieving in only one cell all the names that are recorded in the column A of the Sheet 2, using this simple format : Marlon Brando ; Marilyn Monroe ; Winona Ryder ; etc.

The formula that I am using is working, but I don’t think that this is the best approach, because I have to copy/paste a lot of times the formula then replace the number of the cell, until the last line with content :
=$sheet2.A2& " ; "& $sheet2.A3& " ; " & $sheet2.A4& " ; " etc…

Is there a way to retrieve all the information in a range of cells and printed in the same format ?
Something like :
=$sheet2 .range[A2;A18] & " ; "

I hope this question makes sense…

#2

Try

=TEXTJOIN(";";1;A1:A10)
#3

Hello sokol92,

That should be the formula, but something is wrong in my syntax when I refer to the sheet 2. This is exactly what I wrote
=TEXTJOIN("; "; 1; $‘sheet2’.A2 ; $‘sheet2’.A18)

And I am having some kind of error in the result : #¿NAME?

Do you know what can be ?

#4

See example.
TextJoin.ods (9.0 KB)

1 Like
#5

Ok I was having a language problem because I am working with spanish version of libreoffice, so TEXTJOIN become UNIRCADENAS… I was not expecting that the formulas could change from one language to the other !!!

Thank you very much for your help !!!

#6

No, from English to local.

And vice versa:
example-TextSplit.ods (13.9 KB)
Cases are different … Suddenly it will be needed.

#7

Calc stores|works intern on english-Function-names, but expose the localised Names (depending on the settings by $user) to the user-interface.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.