Is there a function available to allow me to extract nth item from a delimited string.
e.g. if string in Cell A1 is: “It-is-very-cold-outside”, and I want the 3rd item delimted by “-”
then function(A1,"-",3) should give result “very”.
If something like this is not available, is it possible to create such a built-in function.
That would be so good for LibreOffice, since Excel does not have this.
Thanks.
This solution is cleraly recommendable if only specific chips of a compound are needed…
Merging adjacent delimiters can be done by =REGEX(REGEX($A2;"-+";"-";"g");"(?<=^|-)[^-]*";;3) (e.g.)
It’s strange that Calc doesn’t offer a function for splitting strings the way you want. This the more as there is the function TEXTJOIN() for the reverse purpose since (at least) V 5.4, and as the included Basic also offers a Split function.
Because I also often want to split strings, I wrote a bit of very simple user code (mainly) for Calc, providing also an additional control parameter. Below you find a even more simplified version of my code.
Function xTextChip(pDelim As String, pCtrl As Long, pText As String, pIndex As Long)
REM Splits pText, the given string by pDelim, a string occurring zero or more times
REM in pString and returns the chip with 1-based index pIndex.
REM pDelim will not be returned as part of any chip.
REM pCtrl=0 will also count, and probably return, empty text between two occurrences of
REM the delimiter. Otherwise only non-empty parts will count. With other words:
REM Adjacent occurrences of the delimiter will be merged.
REM The helper Sub with its third parameter are provided here in a way to be also usable
REM for a function made to return the split string as an array..
Dim h(), nullified As Long
xTextChip = h
On Error Goto errorExit
splitText = Split(pText, pDelim)
If pCtrl>0 Then compactArray(splitText, "", nullified)
REM (only if the array is to pass on) Redim Preserve splitText(Ubound(splitText)-nullified)
xTextChip = splitText(pIndex-1)
errorExit:
End Function
Sub compactArray(ByRef pArray, pNullify, ByRef pOmitted As Long)
REM pArray is used as a transient parameter (for input and output as well).
REM The parameter pOmitted is only used for output.
If NOT IsArray(pArray) Then Exit Sub
l = Lbound(pArray) : u = Ubound(pArray)
pOmitted = 0
For k = l To u
pull = 0
While (k+pull<=u) AND (pArray(k+pull)=pNullify)
pull = pull + 1
Wend
pOmitted = pOmitted + pull
u = u - pull
If pull>0 Then
For j = k To u
pArray(j) = pArray(j + pull)
pArray(j + pull) = pNullify
Next j
End If
Next k
End Sub
If you find errors, please report.