I noticed someone was manually concatenating a range of cells from a spreadsheet to a database query. How tedious! I knew there was an easy way to automate this busy work. This illustrates the starting point and the desired results: First, I tried the CONCATENATE() function. However, it does not allow a range of cells such as =CONCATENATE(A1:A6). That just yields #VALUE! because CONCATENATE() requires each parameter to be an individual cell such as =CONCATENATE(A1;A2;A3;A4;A6). Beyond that problem, I had these requirements: 1. The length of range is somewhat arbitrary. Sometimes it is 5 and sometimes 5000. 2. A delimiter should be placed between the cells. Instead of 3795 3974 9725 I require 3795, 3974, 9725. 3. I needed to get this problem solved quickly and move on. A solution Remembering the computer science class on algorithms, I devised a strategy using recursion. Here is how to create a reusable template to do the operation:

1. In cell B1, type =A1 .
2. In cell B2, type =IF(LEN(A2)=0;"";B1 & ", " & A2) .
3. Copy cell B2.
4. Highlight cells B3 down to somewhere like B500
5. Paste
6. Save the document as a template (ODF .otc or Excel .xlt format).

Here is how to use the template:

1. Open the template document just created.
2. Copy your cell range from your other spreadsheet.
3. In the new template, paste into A1. (This assumes your data source was vertical. If your data source were horizontal, use Edit > Paste Special with the Transpose option.)
4. Find the last non-blank cell in column B.
5. Use that cell as appropriate (for example, by copying it to another application).

Final results Conclusion It would have been elegant to do this in a more compact way. However, I did quickly accomplish the task without any macro programming, and this method works across platforms. The only retraining between Calc and Excel is that Calc uses semicolons to delimit function parameters while Excel uses commas.