In OpenOffice / LibreOffice Calc, you would normally use CONCATENATE to combine strings from different cells, like in the example below:
A | B | C | |
1 | Combine | This | Combine this |
You can get C1 by combining A1 and B1 using one of the two options below:
=CONCATENATE(A1, " ", B1)
=A1&," ",&B1
But what if you have the following table:
A | B | C | D | E | F | |
1 | 5 | 4 | 5 |
and you want to combine A1:E1, separating each number by a comma, but ignoring empty cells? Unless I am missing something, the two options above won’t work. For example, using the concatenate function:
=concatenate( A1, " ", B1," ",C1," ", D1, " ", E1, " ")
will give you 5,4,,5.
A quick search on the Internet gave me this custom made function STRJOIN, which works perfect. It combines all the text from cells, but unlike the standard concatenate function, it ignores empty cells. Moreover, it lets you define the delimiter as well as strings that go before and after each non-blank entry in the range. To use the same example as above:
=STRJOIN(A1:E1,",")
will give you 5,4,5
or:
=STRJOIN(A1:E1,",","(",")")
will give you (5),(4),(5)
You can only combine text from a array of cells (A1:A10), not from individual identified cells or text like in concatenate (e.g., concatenate(A1,B10,C20, “A”)). But of course nothing stops you from combining the two functions STRJOIN and CONCATENATE, for example:
=CONCATENATE("c(",STRJOIN(A1:E1,","),")")
which will give you: c(5,4,5)
I am copying the macro function below for your (and mine) convenience. Full credit goes to Adam Spiers who wrote this function based on a function by Markus O’Reilly. Check out the post on Stackoverflow for more details.
Function STRJOIN(range, Optional delimiter As String, Optional before As String, Optional after As String) Dim row, col As Integer Dim result, cell As String result = "" If IsMissing(delimiter) Then delimiter = "," End If If IsMissing(before) Then before = "" End If If IsMissing(after) Then after = "" End If If NOT IsMissing(range) Then If NOT IsArray(range) Then result = before & range & after Else For row = LBound(range, 1) To UBound(range, 1) For col = LBound(range, 2) To UBound(range, 2) cell = range(row, col) If cell <> 0 AND Len(Trim(cell)) <> 0 Then If result <> "" Then result = result & delimiter End If result = result & before & range(row, col) & after End If Next Next End If End If STRJOIN = result End Function
Again, check out the original post on Stackoverflow for further instructions (scroll down for the improved version by Adam Spiers).
This work is licensed under a Creative Commons Attribution-ShareAlike 2.5 Generic License.
Pingback: Combining text from data frame in one text string in R | Ecostudies
Pingback: Concatenate in SQLite | Ecostudies
I concatenated two cells (with text) into another cell (c1=b1+d1) but when I highlight the resultant cell it shows the concatenate function instead of the combined text. If I then delete the other two cells, the contents goes away.
Help?
Hi John, Using a formula or function like concatenate will show you the result in the cell. However, when you select (highlight) the cell, the formula rather then the result is shown in the formula bar. This is because the cell contains the formula, not the result. This is also why, if you delete the cells b1 and d1, the formula combines an empty cell with an empty cell = empty cell.
Simply awesome, they dont mention this function enough, i’ve been using the concatenate function in ridiculous ineffcient ways, now with this i can get around it. Thank you!
I had expected the CONCATENATE function to be able to accept a range as well as a list but when I found it didn’t, and it seemed madness to name each cell individally, I figured it must be a common enough need that I could get some clues for a workaround from the internet. Lo and behold, not just some clues but a whole solution in the form of STRJOIN. Cool.
Hi, Im trying to set this up, but it keeps giving me an error when I run the macro. Argument is not optional. Any ideas on what might casue that, sorry bit of a newb to Open office
Hi, I am not sure. You might want to contact the maker of this macro, see the link to the original post on Stackoverflow in the blogpost
Great. Fantastic!
Fantastic solution!!! Just what I was looking for. Thanks for sharing 🙂