Ask Your Question

In Calc how to extract nth item from a delimited string

asked 2019-03-02 01:48:22 +0200

MistayaKh gravatar image

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.


edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-03-02 07:34:19 +0200

updated 2019-03-02 10:19:00 +0200

Something like =REGEX(A1;"(?<=^|-)[^-]*";;3) available in version 6.2+?

edit flag offensive delete link more


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.)

Lupp gravatar imageLupp ( 2019-03-02 19:24:04 +0200 )edit

answered 2019-03-02 15:07:52 +0200

Lupp gravatar image

updated 2019-03-02 15:17:50 +0200

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)
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
  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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-03-02 01:48:22 +0200

Seen: 468 times

Last updated: Mar 02 '19