Add a Split dynamic array function
Add a dynamic array function to split apart a delimited range. See attached file for an example.
Kevin Osborn commented
should allow for designation of all components (array) or just 1 component (non-array) such as 3rd delimited component as an example.
به نمایندگی از بِچا آبودان:
Please add the split function to Excel Dige😂
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, non-array result.
Example: SPLIT("EUMA - Europe - Switzerland - Geneva - Cointrin", " - ", 3) returns the greatest country in the world.
Jon Acampora commented
The SPLIT function would be a great one to have with the new Dynamic Array formula capabilities. The results of the array can now Spill into a range of cells.
richard daniels commented
xll and test sheet with string functions including split
richard daniels commented
I will post a link to an xll that you can use, later, wtih this and other string functions
Yasser Ahmed Hashim commented
I want to add new formula called "split"
The formula will split cells or text upon a delimiter like "split" in vba
=split(delimiter,cell or text)