Concatenate a range of cells in OO Calc

Question

I have column A with these cells:

A1: Apple
A2: Banana
A3: Cherry

I want a formula that will string them together in one cell like this:

"Apple, Banana, Cherry"

Answer 1

As of version 4.1.7 of Apache OpenOffice Calc, there still isn't a simple solution to this problem. CONCATENATE doesn't accept cell ranges, and there isn't a TEXTJOIN function like LibreOffice. However, there is a workaround.

This is essentially a duplicate of pnuts' answer, but with images to hopefully help. His answer explicitly addresses separating the items with delimiters, as well as the opening and closing quotations, as the question above uses. As the general question (how to concatenate a range of cells) is useful to many people, I think my answer should still be useful even though I haven't done that.

In my case, I had one column with letters corresponding to finished worksets, and one column with letters corresponding to unfinished worksets. The letters only appear on every 8th row, so I can't view them all at the same time. I wanted to just mash all the finished letters together in one cell to be easy to view, and the same with the unfinished letters.

The example removes the 7 empty rows per letter and manually inputs which letters are finished/unfinished for convenience.

Column A is the "unfinished" column to be concatenated. Column C is used to perform the concatenation. Row 2 is the first row, and row 24 is the final row. G1 shows the concatenated result in an easy-to-see spot near the top of the document.

Columns B and D, and cell G2, utilize the same method to show the "finished" data. The formulas aren't shown here.

In cell C2, point explicitly to A2:
=A2

If you may have blanks, as I do, there needs to be a conditional in C2 to treat the first cell as blank text, instead of as zero ^{Note 1}:
=IF (A2 <> "" ; A2 ; "")

Then, in cell C3, concatenate C2 and A3:
=C2 & A3

Copy C3, then highlight C4:C24 and paste the formula to autofill those cells.

Wherever you need the result of the concatenation, reference C24.

Notes

^{Note 1} If N cells at the top of the A row are blank and you just let C2 = A2, the first N rows on C will show 0, and a single 0 will be prepended to the concatenation result. Here, columns B and D are used to illustrate the problem:

Answer 2

I don’t know if it’s implanted on OpenOffice but on his cousin LibreOffice Calc since the version 5.2 you’ve got the function : TEXTJOIN

TEXTJOIN( delimiter, skip_empty, string1[, string2][, …] )

delimiter is a text string and can be a range.

skip_empty is a logical (TRUE or FALSE, 1 or 0) argument. When TRUE, empty strings will be ignored.

string1[, string2][, …] are strings or references to cells or ranges that contains text to join.
Ranges are traversed row by row (from top to bottom).

Example : =TEXTJOIN(",",1,A1:A10)

More info here : https://help.libreoffice.org/6.3/en-US/text/scalc/01/func_textjoin.html?DbPAR=CALC#bm_id581556228060864

Answer 3

A different approach, suitable for a long list, would be to copy A1 to B1, prepend a " and in B2 enter:

 =B1&", "&A2&IF(A3="";"""";"")

then double-click the fill handle to cell B2 (the small square at its bottom right). The result should appear in ColumnB in the row of the last entry of your list.

Answer 4

Either use the CONCATENATE function or ampersands (&):

=CONCATENATE("""", A1, ", ", A2, ", ", A3, """")

For something more powerful, write a Basic macro that uses Join.

EDIT:

There is no function that can concatenate a range. Instead, write a Basic macro or drag and drop CONCATENATE formulas to multiple cells. See https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=5438.