Bug 97101 - Add a SPLIT() function for separating string cells based on a delimiter
Summary: Add a SPLIT() function for separating string cells based on a delimiter
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Hardware: All All
: medium enhancement
Assignee: Not Assigned
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
Reported: 2016-01-13 14:40 UTC by David
Modified: 2022-09-08 22:15 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:

Sample with matrix formulas to split cells. (18.33 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2016-01-13 22:17 UTC, m.a.riosv

Note You need to log in before you can comment on or make changes to this bug.
Description David 2016-01-13 14:40:49 UTC
Add a split function for separating sting cells based on a delimiter. SAS and T-SQL uses a function like this and now I see one for Google docs here: https://sites.google.com/a/slu.edu/tips/home/combiningandsplittingdatainspreadsheets

This is a common problem for those of us who work with data day in and day out on small data outside of a large database where the data is not columnar with consistent length parameters. For instance: I concatenated these in one instance and later need to split them else where:

Lube Mechanic - John Doe
Technician - John Singleton

As you can seen the delimiter options could be: "-" or " - "

Thank you for your continued excellence in improving this suite.
Comment 1 m.a.riosv 2016-01-13 22:17:36 UTC
Created attachment 121911 [details]
Sample with matrix formulas to split cells.

Hi @David in the attached file you can find how to do it with a matrix formula, in different ways.

I guess you know about Menu/Data/Text to columns.
Comment 2 Buovjaga 2016-01-19 17:54:58 UTC
David: are you happy with Miguel's proposal(s)?
Comment 3 David 2016-01-22 03:47:52 UTC
I understand Miguels proposal and I appreciate the assumption that I am not ignorant. I am familiar with the tactile mouse functions of the GUI Text to Columns approach, which does not process live changes. However, my need was a bit deeper and involved nesting functions. To explain my case and familiarity with spreadsheets and functions here is the code I used to solve my problem.


As you can see I was able to work the issue through and the solution was more complex than previously revealed. As I created the solution I also realized the complexity of nested functions for the average person. My belief is that spreadsheets are for common office suite users who have context technical needs and usually lack the programming skills and logic (years in education and an office setting as the IT go-to person).

My philosophy leads me to believe that this solution is out of the normal spreadsheet user's range of skills.

As I found that this new function was added to Google docs - I thought I should forward the proposal.

If you agree with my beliefs about the usability of a spreadsheet in a common person's hand  - then perhaps you share a similar philosophy as well and this functionality should be added to improve the product.

I also believe that Google docs is a competitor to stay abreast of more than Microsoft these days. Most MS users I know frequently expressed more dissatisfaction with each new MS product release they were forced to use. They were never allowed to load a Linux migrated package to try. Too bad some of us focus too heavily on MS as the competitor.

Thank you for your consideration of my proposal!
Comment 4 Buovjaga 2016-01-24 17:49:56 UTC
Ok, setting to NEW.
Comment 5 Parijatha Kumar 2016-10-25 07:04:02 UTC
I was also about to open a feature request for this feature. I completely agree with David's point of view.
Comment 6 Wolfgang Jäger 2019-11-04 21:33:51 UTC
Starting with version 5.2.0 (2016-06-23) we have a TEXTJOIN() function, and since 5.4 it is well debugged.  
3 and a half years later it is still crying for its counterpat function TEXTSPLIT(). 

I would suggest to not only port the Basic function Split to Calc, but to do something like (for simplicity in Basic-like notation):

Function TEXTSPLIT(pDelimiter, pMode, pList)
REM pMode should -at least- work like the second parameter of TEXTJOIN() does.
In Addition it may offer to suppress repetitions (pMode<0 e.g.)

A premium implementation would also accept array evaulation for ONEdimensional (1 row / 1 column= pList arrays, probably corresponding with 1D pDelimiter array with output to a 2Drange readable as a 1D array of columns or a 1D array of Rows respectively. (I did it in Basic.)
Comment 7 Wolfgang Jäger 2019-11-04 21:42:13 UTC
Forgot to mention that TEXTSPLIT() will need to output an array and will therefore have to lock an output range if called AS a formula. The well known problems concerning output ranges no longer actually meeting the needs of changed input, should be relieved by a fourth parameter giving a minimum length of the output for one split list. Spare cells: empty text or (more logical) N/A.
Comment 8 Jürgen Kirsten 2020-06-14 11:32:45 UTC
I should like to support David's question.

I do not understand why it is not being implemented. In VBA the Split function already exists. This should make it much easier.
I have written a small function that has helped me many times with this problem.

Function SEP(text_chain, separator, chain_link)
' Function from Juergen Kirsten (mail: dscheikey@gmail.com)
' This function separates a text at the deffined separator and returns the nth link.
' text_chain = String with separators
' separator = Sign ore signs in double quotation marks
' chain_link: The number of the chain link
On Error GoTo Errorhandler
i = UBound(Split(text_chain, separator)) + 1
If chain_link > i Then SEP = ""
SEP = Split(text_chain, separator)(chain_link-1)
End Function