TEXTJOIN(), TEXTSPLIT() an RFC

[Lupp]

OO.o/AOO never inmplemented one of the mentioned funtions.
Because I needed the functionality more than once, I wrote a TEXTJOIN() [now XTextjoin()] in Basic for my personal use and additionally a XTEXTSPLIT().
With V4.0 LibO had a TEXTJOIN() and since V4.2 it works reasonably.

LibO V 25.2, so I was told, will also have a TEXTSPLIT(). It's mainly for compatibility with recent Excel. Because I was dissatisfied with the parameter list according to Excel help, I wrote something ‘better’.

Now I'm requesting your comments on the code (calcTextsplit only) and the explanations contained in the attachment together with a few usage examples.

calcTextSplit_V0_1Rev1_RFC_AOO.ods

(20.79 KiB) Downloaded 237 times

(I reworked my original examples a bit to allow also execution under AOO.)
[EDIT 2024-10-13 about 16:20 UTC]
Please note that my post from 2024-10-08 19:30 UTC contains a reworked version of the example with a kind of specifiaction roughly in the style of OASIS ODF papers.[/Edit]
BTW: As always my mentioned code [also XTEXTJOIN() and XTEXTSPLIT() not contained in the attachment] is without a license. Simply ask for the code if interested

[Bidouille]

Old issue: https://bz.apache.org/ooo/show_bug.cgi?id=84516

But in Basic, these functions can not be embedded in the formula editor.
The idea will be to build an OXT in Python language (like this add-in)

[Lupp]

@Bidouille:
Thanks for your response.
However, I didn't actually understand clearly what you want to tell.

OO.o/AOO:
The old bug report is about OO.o 1.1. It is marked "CONFIRMED" , but there isn't a fix.
I visit that bug site very rarely and use AOO only for testing in some cases.
However, there still (V4.1.7 for me) is neither a CONCAT() nor CONCATENATE() function accepting arrays (cell ranges or respective results of subexpressions) as an argument. There also isn't a TEXTJOIN().
LibO:
There are the functions CONCAT() and TEXTJOIN() since V4.0/4.2 accepting array arguments, but neither has an additional parameter to choose "first down the column" or the opposite.
OO.o, LibO (up to versions 24.8):
No TEXTSPLIT() function or similar.

But in Basic, these functions can not be embedded in the formula editor.

Do you mean the 'Formula Wizard'? I rarely use it.

The idea will be to build an OXT in Python language (like this add-in)

Sorrty. I'm not capable of propgramming in Python, and I never made an .oxt extension.
I won't enter such a new field at the age of 80.

Did you also check my attachment?
Still interested in you comments insofar.

[MrProgrammer]

Now I'm requesting your comments on the code (calcTextsplit only) and the explanations contained in the attachment together with a few usage examples.

The biggest difficulty I see is that neither the purpose of the function nor the behavior of its parameters is explained. As someone who has used Calc for a long time I can imagine how the function probably works, though I'd need to examine the program or run tests to know for sure. Many people will not be interested in or capable of doing that.

Just as an example let's consider the first parameter pColDelim:
• Is this parameter a delimiter string or a set of single delimiter characters?
• Does it accept an array of delimiters?
• Is it affected by option Calc → Calculate → Enable regular expressions in formulas?
• If the parameter is the empty string, does that mean function will separate each letter in the row?

I'm not looking for answers, just suggesting a few of the details that your function documentation should cover. I think people won't be much interested in an undocumented function, even though it's useful. You do provide examples, but I would want actual documentation, since it is difficult to get that information just from examples. I am happy to offer to review English-language documentation, as I know that English is not your first language (though most people would not suspect that).

[Lupp]

Thanks for the response!

The biggest difficulty I see is that neither the purpose of the function nor the behavior of its parameters is explained. As someone who has used Calc for a long time I can imagine how the function probably works, though I'd need to examine the program or run tests to know for sure. Many people will not be capable of doing that.

You are right, of course, but the RFC was addressed to contributors expected to be capable of reading and understanding the code and the examples.
A "polished" or "released" version would need to come with some kind of documentation.

Just as an example let's consider the first parameter pColDelim:
• Is this parameter a text string or a set of single characters?
• Does it accept an array?
• Is it affected by option Calc → Calculate → Enable regular expressions in formulas?
• If the parameter is the empty string, does that mean function ... will separate each letter[/url] in the row?
I think people won't be much interested in an undocumented function, even though it's useful. You do provide examples, but I would want actual documentation, ...

The attached file wasn't intended to be a releasable and documented "piece of software". The "explanations" contained a lot of parts in italic which were mainly intended to clearly tell what kind or subtopics of comments were requested.
However,
first and second bullet: pColDelim (pRowDelim also) is not a set of alternative delimiters and an array can't be accepted.
third bullet: No. (If support for RegEx should be requested, it needed to be enabled per call, not for the module. This would require the internal usage of a REGEX() function like it is available in LibreOffice V6.2 or higher.
fourth bullet: I wanted to delegate this to the future design decisions for the helper function textSplit1D
To provide a ready made documentation would make sense if the publisched code were a "release candidate".
Discussing all the alternatives would have made the request very long.
I reworked the attachment to give some "documentation" roughly in OASIS style.

calcTextSplit_V0_1Rev1_RFC_AOO_reworked.ods

(28.5 KiB) Downloaded 191 times

Thanks again!

[Lupp]

My posts above were placed under "General Discussion" because the code seemed a bit too long to be posted as a "Snippet", and was of little value without a .ods file containing examples and some explanation (contained in the second attachment).
But mainly I was interested in comments by experienced users.
I got few, but my second example was downloaded 190 times (up to now: 2025-11-23).
Therefore I assume that there might be interest in the functionality, and probably in enhancements or fixes.

My occasional usage of the functions showed me recently that there was a wee little bug (2 times).
I fixed it and took the opportunity to also make changes allowing the code to run under AOO and under very old versions of LibO.
This required to remove the internal usage of the LibO version of TEXTJOIN() and of REGEX().

Now I offer an enhanced example for download:

calcTextJoinSplit_1_0_AOO_111961.ods

(32.45 KiB) Downloaded 6 times

I AM STILL INTERESTED IN COMMENTS.

[Lupp]

Sorry!
There were again flaws.
I wasted a few hours with research and a (rather sloppy) fix.
However, I will no longer try to get code running in AOO or in very old versions of LibO.
What I now have is "successfully" tested with many versions of LibO >= 7.0.

The reasons for failure were two mistakes by myself - and the very inconsistent treatment of empty (blank) cells in old versions including AOO: A simple cell reference to a blank cell returned numeric 0 and ranges did so per cell.
Most likely this nonsense can't be changed because that would break old sheets and (supposedly) compatibility with Excel.
However, LibO >= 7.0 changed the treatment for references to a cell range.
There an UDF gets a Variant/Empty per cell and calc gets something answering FALSE on "number?", on "text?" and also on "blank?".

Result: Confusion.
If you are still patient, you may try and comment on

calcTextJoinSplit_sloppyFix.ods

(31.99 KiB) Downloaded 3 times

.
You may also try the "original" version:

calcTextJoinSplit_1_0_LibO7_3_6orHigher.ods

(29.71 KiB) Downloaded 3 times

[Villeroy]

Old issue: https://bz.apache.org/ooo/show_bug.cgi?id=84516

But in Basic, these functions can not be embedded in the formula editor.
The idea will be to build an OXT in Python language (like this add-in)

The pystring extension, published on this forum by Hubert Lambert, can do the joining and splitting among many other things. It might be trivial to split PYSTRING("function_name";...) into separate functions.

[Lupp]

Did you study my "original solution" above?