StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Thanks a lot Markus for finding a solution to this.</p> <p>Here are some slightly more detailed instructions for the benefit of OpenOffice Basic newbies like myself. This applies to version 3.1:</p> <p>Tools -> Macros -> Organize Macros -> OpenOffice.org Basic...</p> <p>Now select from the explorer tree where you want your function live, e.g. it can be in your own macro library (My Macros / Standard) or stored directly in the current spreadsheet.</p> <p>Now enter a new Macro name and click New to open the OO.org Basic IDE. You'll see a REM statement and some stub Sub definitions. Delete all that and replace it with:</p> <pre><code>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 </code></pre> <p>The above code has some slight improvements from Markus' original:</p> <ul> <li><p>Doesn't start with a delimiter when the first cell in the range is empty.</p></li> <li><p>Allows optional choice of the delimiter (defaults to ","), and the strings which go before and after each non-blank entry in the range (default to "").</p></li> <li><p>I renamed it STRJOIN since "join" is the typical name of this function in several popular languages, such as Perl, Python, and Ruby.</p></li> <li><p>Variables all lowercase</p></li> </ul> <p>Now save the macro, go to the cell where you want the join to appear, and type:</p> <pre><code> =STRJOIN(C3:C50) </code></pre> <p>replacing C3:C50 with the range of strings you want to join.</p> <p>To customise the delimiter, instead use something like:</p> <pre><code> =STRJOIN(C3:C50; " / ") </code></pre> <p>If you wanted to join a bunch of email addresses, you could use:</p> <pre><code> =STRJOIN(C3:C50; ", "; "<"; ">") </code></pre> <p>and the result would be something like</p> <pre><code><foo@bar.com>, <baz@qux.org>, <another@email.address>, <and@so.on> </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload