Sheetside 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/extractwords.htm.
20 comments

Sergei Baklan commented
That could be like
=TRANSPOSE(FILTERXML("<r><n>" & SUBSTITUTE(A1,",","</n><n>") & "</n></r>", "//n"))
If extract, for example, 2nd word
=FILTERXML("<r><n>" & SUBSTITUTE(A1,",","</n><n>") & "</n></r>", "//n[2]") 
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.

Kenneth Barber commented
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/304921excelforwindowsdesktopapplication/suggestions/39503848addasplitdynamicarrayfunction 
Kenneth Barber commented
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 & fieldmanagement functions which I use constantly. It would be helpful to have these hardcoded 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.

Kenneth Barber commented
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.

Kenneth Barber commented
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 
Kenneth Barber commented

Kenneth Barber commented
And it's already supported in FormulaDesk...