I am using open-spread spreadsheets program and I am trying to match several text cells with delimiter . For example, suppose I have cells below me:
+ -------- + | Cell 1 + -------- + | Cell 2 + -------- + | Cell 3 + -------- + | Cell 4 + -------- + | Cell 5 + -------- +
I would like to add them to the delimiter so that the result is in the same cell:
+ --- ------------------------------------------- + | (Cell 1), (cell2), (cell3), (cell 4), (cell 5) + ------------------------- --------------------- +
My first thought was to try a macro or something, but not me It seems that the Open Office supports those people. Any ideas?
Thanks a lot for finding a solution on this.
Here are some slightly more detailed instructions for the benefit of OpenOffice Basic New Newfone, as it applies to version 3.1 itself:
Tools -> Macros -> Macros Organize -> OpenOffice Basic ...
Now choose from the Explorer tree where you want your function to be live, such as it may be in your own macro library (micros / standard) or in the current spreadsheet Directly stored Can.
Now enter a new macro name and click new to open the OOG original IDE. You will see a REME statement and some stub sub definitions. Delete all and replace it with:
function STRJOIN (optional, delimiter in the form of a string, alternate alternate form of string), the colon, If the Ismissing (delimiter) then the isamissing (delimiter), then if the (if first) IsMissing is the first = "" end if if after IsMissing (after) = "end" if not ifmissing (Range) So if not IsArray (Category) then result = First & amp; Range & amp; For the line = LBound (range, 1) Ubound (category, 2) cell = range (line, call) for col = l bound (range, 2) for Ubound (category, 1) if cell & lt; & Gt; 0 and lane (trim (cell)) & lt; & Gt; 0 Then if the result is & lt; & Gt; "" Result = result & amp; Delimiter end if result = result & amp; First & amp; Category (line, call) & amp;
The code above has some minor improvements from the origin of Marcus:
-
The first cell in the range is empty when not before a limit it happens.
-
Allows alternative options of delimiter ("," "default for"), and which are before and after each blank entry in the wire range (by default "").
-
I named it "STRJOIN" because "function" The common name of this function is in many popular languages like Perl, Python and Ruby.
-
All lowercase variables
To save the macro now, go to the cell in which you want to join to join, And type:
= STRJOIN (C3: C50)
to move the C3 you want to add.
To customize the delimiter, use something instead:
= STRJOIN (C3: C50; "/")
If you want to join a group of email addresses, you can use it:
= STRJOIN (C3: C50; ","; "& LT; ";" & Gt; ")
and the result will be something
