===EDIT 2024-07-27===
Thanks to @cwolan for the correction.
===/EDIT ===
A counterpart is still missing.
(AOO has no TEXTJOIN(), and consequently no reverting function.)
Code: Select all
REM ***** BASIC *****
Option Explicit
REM A simple version. The returned result is a sequence of stings.
REM Using the function in Calc with forced array evaluation it will go to a cell strip.
Function udfTextSplitStrip(pSeparator As String, pOmitEmpty As Boolean, pWorkString, Optional pMinLength As Long)
Dim wSequ, u As Long, m As Long, n As Long, j As Long, j_ele As String
If IsMissing(pMinLength) Then pMinLength = 0
wSequ = Split(pWorkString, pSeparator)
u = Ubound(wSequ)
n = -1
For j = 0 To u
j_ele = wSequ(j)
If (j_ele<>"") OR NOT pOmitEmpty Then
n = n + 1
wSequ(n) = j_ele
EndIf
Next j
m = IIf(n<(pMinLength - 1), pMinLength - 1, n)
If m>=0 Then
Redim Preserve wSequ(m)
For j = n + 1 To m
wSequ(j) = ""
Next j
EndIf
udfTextSplitStrip = wSequ
End FunctionUsed for direct output to a spreadsheet the function will only make sense if array-mode is forced.
It will then lock an output range. To get a kind of relief for the problem with array output of variable length it has a parameter allowing to set a minimal length. Currently unneeded cells at the end will then get the empty string. Overflow may occur, of course.
A little demo:
