Posted: Thu Dec 03, 2009 1:46 pm Post subject: Concatenating cell contents from range (a1:a2 - not a1 &
I'm looking for a way to concatenate contents of a range of cells into a single cell. The ranges are variable depending on content so using fixed cell addresses such as in
Code:
concatenate(a1;a2)
will not work.
There are tens of ranges to be concatenated per sheet so while using a script to find, copy, temporarily paste, merge, copy contents, delete temporary cells and then paste the clipboard to the destination cell may be feasible, it would be an ugly, very labour intensive and inelegant solution.
Ideally I'd like to use a function that would look something like
Code:
concatenate(a1:a2;", ")
where the second parameter defines the separator and the range is generated using e.g. address() column() row() and match() functions as in:
I am not sure if it is possible to do what you are trying to do, but if it is possible you will need to generate a list of cells instead of ranges so that your formula looks like the following. The following example assumes that you want to concatenate three ranges: A1:A2, E3:E6, and N100:N104:
The problem that I see is that while you can use functions to create the list of cells, it appears that the concatenate function only concatenates the value of any formulas or cells that you pass as arguments to the function and you cannot tell the concatenate function that the functions passed in its arguments is really a list of cells.
What you might consider doing is writing a macro that uses a while loop to iterate through the various possible cells ranges and for each cell that the loop evaluates, test the cell's value for a match and if it matches, append the cell's value onto a string variable.
I will admit that my OpenOffice.org Basic programminig skills are very weak so I cannot write the macro for you, but I don't think you would need to create temporary ranges and do any copying and pasting.
I don't know if this helps, but I found this forum topic in the OpenOffice.org support forums. In that topic, they have some sample code for iterating through a range of cells in a while loop and then, later in the topic, they fetch a cell from its address. That topic changes the formula in a range of cells, but once you fetch a cell you should be able to get the cell's text value and concatenate it onto a variable that you return at the end of your macro.
Thanks - I'll take a very close look at that thread.
I'd also appreciate some information on what the tilde operator does and how it can be used. The help file says:
~ (Tilde)
Concatenation or union
Takes two references and returns a reference list, which is a concatenation of the left reference followed by the right reference. Double entries are referenced twice. See note below this table.
Note:Reference concatenation using a tilde character is a new operator, available starting with NeoOffice 3.0 and StarOffice 9. When a formula with the tilde operator exists in a document that is opened in previous versions of the software, an error is returned. A reference list is not allowed inside an array expression.
That looks somewhat promising. However, no matter how I try to use it I just get errors.
That looks somewhat promising. However, no matter how I try to use it I just get errors.
The "~" operator does not work because it works like the ":" operator. The difference is that a if you sum(A1:A6), you will get the sum of all cells from A1 through A6. In contrast, if you sum(A1~A6), you will only get the sum of cells A1 and A6 and all of the cells between those two cells will be ignored.
The only thing that the "~" operator seems to provide is the ability to pass a list of disconnected cells (for example, A6~B10~C20) into a function that expects a range. Since the concatenate() function does not accept ranges as arguments, the "~" will produce an error in your case.
For now I ended up using a long string of nested if() functions. While ugly it does work. I'll work on a more elegant solution when things get a bit less hectic.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You cannot download files in this forum
