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.
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.
David: are you happy with Miguel's proposal(s)?
Hi, 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. =RIGHT(VLOOKUP(B$36,$A$3:$AC$30,2),LEN(VLOOKUP(B$36,$A$3:$AC$30,2))-FIND("-",VLOOKUP(B$36,$A$3:$AC$30,2))-1) 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!
Ok, setting to NEW.
I was also about to open a feature request for this feature. I completely agree with David's point of view.
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.)
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.
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) Errorhandler: End Function Juergen