Add a Split dynamic array function
Add a dynamic array function to split apart a delimited range. See attached file for an example.
24 comments

Excel Wizard commented
Please try
=INDEX(TRANSPOSE(FILTERXML("<b><a>"&SUBSTITUTE(TEXTJOIN("",,A1:A5),"","</a><a>")&"</a></b>","//a")),SEQUENCE(ROWS(A1:A5),LEN(A1)LEN(SUBSTITUTE(A1,"",))+1))

Sergei Baklan commented
As a workaround that could be
=TRANSPOSE(FILTERXML("<r><n>" & SUBSTITUTE(A1,",","</n><n>") & "</n></r>", "//n")) 
Kenneth Foss commented
This would be a good addition to Excel!
Thank you very much :) 
Roy commented
The following can find a desired instance number in a delimted data cell:
=MID(SplitSource,SMALL(IFERROR(SEQUENCE(1,LEN(SplitSource))/IF(MID(SplitSource,SEQUENCE(1,LEN(SplitSource)),1)=SplitDelimiter,1,0),""),SplitDesiredElement1)+1,SMALL(IFERROR(SEQUENCE(1,LEN(SplitSource))/IF(MID(SplitSource,SEQUENCE(1,LEN(SplitSource)),1)="$",1,0),""),SplitDesiredElement)SMALL(IFERROR(SEQUENCE(1,LEN(SplitSource))/IF(MID(SplitSource,SEQUENCE(1,LEN(SplitSource)),1)=SplitDelimiter,1,0),""),SplitDesiredElement1)1)
It just looks brutal. Lots of repetitive stuff.
One could add error checking to make sure the SplitDesiredElement value does not exceed the count of elements.
Since the elements are simple and repeated a time or two, it lends itself to simplification of appearance via Named Ranges yet would still be easy to maintain.
I suppose ways around the problem have existed for years. I just don't know them.
It has two basic components, to find the nth (desired) delimeter, then subtract 1 for the one preceding it, then to return what's in between. Could be altered to allow more than one segment to be returned. 1st, last, any particular one, a count, all can be achieved via COUNT() acting upon the element inside IFERROR().
It would fail if a delimiter were the first character, and if there is more than one contiguous delimiter (like in "abc $$ def$gh" if the "$" were the delimiter).
The internal array produced is of the positions of the delimiter so it can feed directly into anything working off position. A multicharacter delimiter is possible if one uses LEN(SplitDelimiter) for the early "+1" and very end's "1" as well as the length of return for the first and last MID()'s but not the middle one.
Anyway, possibilities seem to exist in it. Someone usefully simplifying it would be handy. Until MS programs in a proper function!

Tim Ellis commented
I can see this formula being very useful, thanks.

Anonymous commented

Cristiano Galvão commented
I totally agree that we should have a SPLIT function.

Kevin Osborn commented
should allow for designation of all components (array) or just 1 component (nonarray) such as 3rd delimited component as an example.

Sajede commented
به نمایندگی از بِچا آبودان:
Please add the split function to Excel Dige😂 
Anonymous commented
👌👌

Brad Yundt commented
Every now and then I answer a forum question on Google Sheets. In so doing last week, I found out they already have a Split function.
I really hate ceding high ground to Google Sheets. Excel definitely needs to catch up. Give us a Split function.

Roger Govier commented
This would be a great addition

Wyn Hopkins commented
This is a great idea for Dynamic Arrays. Optional parameters covering:
split at left most / right most delimiter
Number of splits 
David Abiola commented
Absolutely. It would be a great addition to the DA family functions

Sergei Baklan commented

Jon Peltier commented
This would be a great addition to the Dynamic Array family of functions.

Vaibhav Garg commented
Assuming the text is in A2, this should work:
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),SEQUENCE(500,,1,100),100))The delimiter is in the 2nd literal argument of substitute.

Friedrich Geyer commented
Maybe useful: http://www.excelformeln.de/formeln.html?welcher=210

Yoshihiro Sato commented
Everyone, thank you for suggesting the SPLIT function!
In addition, I thought that it would be nice to have the following format.
= split (delimiter, cell or text, [output is vertical or horizontal)]
In the case of vertical, the output will look like B1, B2, B3.
In the case of horizontal, the output is like the cells B1, C1, D1.
The default is that the horizontal is easy to use. 
Mark Fitzpatrick commented
I've built this function, but would love to have it native. I agree with Jon  the value increases with Dynamic Arrays. I would do the function a little differently that proposed above. It could operate more like the VBA function. SPLIT(Text, Delimiter, [Field]). The optional Field would allow the function to return a single, nonarray result.
Example: SPLIT("EUMA  Europe  Switzerland  Geneva  Cointrin", "  ", 3) returns the greatest country in the world.