Add a Split dynamic array function
Add a dynamic array function to split apart a delimited range. See attached file for an example.
Jeremy
shared this idea
30 comments
-
Kenneth Barber commented
Frank, the Text To Columns feature produces a static output, cannot be used in formulas, and must be initiated manually. It is good, but it isn't enough. That is why everyone wants a SPLIT/TEXTSPLIT function.
-
Frank
commented
From the example I see here, Excel already has this feature, but it is called "Text-to-columns" and is found under the Data in the ribbon. This function has both a fixed position and a spatial delimiter that works with commas, spaces, or other indicators. It appears to do what you want, at least from the example offered in this forum.
-
Frank
commented
From the example I see here, Excel already has this feature, but it is called "Text-to-columns" and is found under the Data in the ribbon. This function has both a fixed width and a spatial delimiter that works with commas, spaces, or other indicators. It appears to do what you want, at least from the example offered in this forum.
-
Chayse
commented
Trying to upload again
-
Chayse
commented
I have a module that does that, you're welcome to it if you want, the function is:
SPLITSTRING(array, optional delimiter)I built it to utilize the built in VBA function "SPLIT" that apparently is not available as a worksheet function. It's also got a bunch of other stuff I've worked on in there too.
This is mostly a prototype, but I have worked out most of the bugs I can find.
I tried to upload it here, but I guess I can't?
-
Joe
commented
I struggle to understand why DA was shipped without SPLIT function.
-
Patrick Matthews
commented
The workaround to use FILTERXML is absolutely brilliant! That said...please do give us a real Split function, similar to the one in VBA. And yes, please do give us those optional 3rd and 4th arguments to limit the number of "splits" performed, and/or control the case-sensitivity.
-
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 :-) -
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 (non-array) 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
