Hi All,
Is there any split function in LibreOffice something similar to SPLIT in google spreadsheets?
I really need to split a large text based on few delimiters.
Please help me.
Presumably you are getting this from an external file? Can you not set the import filter to split as required?
Neither Excel nor Calc offer a Spli function. LibreOffice BASIC does-
Even using BASIC it is simple therefore to code a usable SPLIT() function for Calc. There are a lot of possible enhancements.
Example code:
REM ***** BASIC *****
Function textSplit(pDelim As String, pSkip As Long, pText As String, Optional pMinCount As Long)
' pSkip is inserted to allow for enhancemnts without a need to rearrange old calls.
' A "0" (zero) passed as a string via pText is supposed to be produced by silly automatims.
' pText is set to "" then.
If pText = 0 Then
pText=""
End If
REM ** theArray = Split(pText, pDelim)** **USELESS LINE** ** See comment!**
If IsMissing(pMinCount) Then
pMinCount=1
End If
theArray = Split(pText, pDelim)
If Ubound(theArray)<(pMinCount - 1) Then
Redim Preserve theArray(0 To pMinCount-1)
End If
textSplit = theArray
End Function
How to use it is demontraded in this attached spreadsheet document.
(Editing with respect to the comment by @JohnSUN. Thanks!)
The code above was rectified. The attachment-link above now points to a file containing rectified code.
===Edit 2020-10-08 about 21:00 UTC===
The answer is very old.
Meanwhile (since V6.2) there is the REGEX() function which often can be used as a surrogate for the missing SPLIT().
If somebody actually wants to have a custom function in Basic for the purpose, it should also implement a few enhancements like the already mentioned skip (omit empty parts; omit repetitions e.g.).
If I get comments here emphasizing the need, I will consider to suggest an updated solution next weekend.
IMHO the first line theArray = Split(pText, pDelim) is not needed
@JohnSun is right, of course. (I wrote the code from scratch under bad -bed- circumstances. Finally I missed the silly dub.)