Sheet-side Split function
VBA has a Split function that returns a string array consisting of the pieces of the string parameter after cutting it at a given delimiter. It would be nice to have it available on the sheet side of Excel.
It could be used for easy string parsing when paired with INDEX without having to, say, come up with a formula that takes the string between the 3rd and 4th ", ". Just check out how hard that can be: http://www.ozgrid.com/Excel/extract-words.htm.
Kenneth Barber
shared this idea
19 comments
-
Leif Hille
commented
Kenneth, actually my “Sum_Fields2D - returns a sum of an array along a specified dimension” takes a text matrix input and sums elementwise the values along the specified dimension. So MxN array summed on 1st dimension provides 1xN array of values (each is a sum of the M values in that row) and conversely summing on second dimension provides Mx1 result vector of sums of the N values in each column.
This is super helpful in aggregating arbitrary sized text matrices.
-
Even though my suggestion came first, this suggestion should be merged with the one linked below, since the latter has better wording and more votes.
https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/39503848-add-a-split-dynamic-array-function -
In response to Fabio, your formula extracts only the last word of a string. It does not split the string, at each occurrence of the delimiter, into an array of words. It is still helpful, but we're going after something more generalized.
In response to Leif, it sounds like your Join function is like TEXTJOIN and createEyeMatrix is like MUNIT. Reverse can be simulated with CONCAT(MID(<string>,LEN(<string>)-SEQUENCE(LEN(<string>),,0),1)). Sum_Fields2D can be simulated with SUM(INDEX()), using 0 as an index to represent a whole row or whole column. Really, we just need Split and Reverse.
-
Leif Hille
commented
I have stock set of VBA excel sheet string & field-management functions which I use constantly. It would be helpful to have these hard-coded into excel.
Split - splits a string into 1D Array (delimiter specified)
Split2d - splits a string into a 2D array for matrices (delimiters specified)
Get_Nth_Field - picks out a Nth value in a 1D Array String (delimiter specified)
Sum_Fields2D - returns a sum of an array along a specified dimension
Join - join arrays to string - works on 2D and 1D arrays (delimiters specified)
Reverse - reverses a string
RepeatStr - Repeats a specified string N times ==> this function exists now: REPT
createEyeMatrix - useful for creating numerical matrix with 1s on diagonal -
Sergei Baklan
commented
Fabio, I guess practically all of us use such formula, but SPLIT() will be much more suitable
-
Anonymous
commented
Top
-
Fabio Baldini commented
Hi Kenneth I used this formule - =TRIM(RIGHT(SUBSTITUTE(A2;" ";REPT(" ";LEN(A2)));LEN(A2))) Best Regards
-
Rodrigo Aiosa commented
True
-
Fernando Fernandes
commented
I agree that a split function to return an array based on a delimiter, to be used in array formulas, would be very much welcome to Excel interface.I'm forwarding this link to a bunch of people...
-
Sergei Baklan
commented
like =SPLIT(<text>,<delimiter>,[<splitby>],[<splitto>)
where
splitby
-1 by first delimiter
0 by each delimiter (default)
1 by right delimitersplitto
TRUE to columns (default)
FALSE to rows -
Jon Peltier
commented
This would be a great addition to the Dynamic Array family of functions.
-
There is even less excuse to not have this function now that array formulas are getting even easier thanks to dynamic arrays.
-
Jon Wittwer
commented
I would love to see the SPLIT function added to Excel ... and it should be exactly compatible with the Google Sheets version.
To following formula returns the middle name:
=INDEX(SPLIT("J. Allen Smith"," "),2) -
Joseph T
commented
Google Sheets has this functionality, along with an INDEX function, which makes it very easy to split a cell, and then return the nth item of the resulting array. Adding a TEXTSPLIT function (in line with the TEXTJOIN naming would be really great!
-
Michael Bishop
commented
I'd love to have a TextSplit function in Excel. I'd use it heavily, and my Excel files would be much cleaner.
-
Charles, we already have the JOIN functionality with TEXTJOIN. All we need is SPLIT.
-
Charles
commented
It will take you two minutes and save people a lot of time.
SPLIT("Text","x") should return an array {"Te","t"}
SPLIT("Text","ex") should return an array {"T","t"}
SPLIT("Text","e",1) should return "T"
SPLIT("Text","e",2) should return "xt"JOIN(A1,B1,C1,"-") should return the content of cells A1, B1, C1, etc concatenated and separated with "-" (i.e. the last parameter of the function)
JOIN(A1:C1, "-") would behave as you expect -
-
And it's already supported in FormulaDesk...