Add a Split dynamic array function
Add a dynamic array function to split apart a delimited range. See attached file for an example.
Trying to upload again
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?
I don't wonder so much as I think I've got an inkling.
A little while back I decided I'd never come up with a really compact splitting workaround using the ideas that came to mind so I thought I'd better internalize the FILTERXML() technique. I need to learn it to make the web scraping ability work nicely so that added its own impetus.
One thing I'd not run into in examples until properly researching it was the ability to pick which element you'd like to split out, the eight word in a sentence for example. But in considering an example of that, I realized something odd about FILTERXML()'s parameter use.
To pick the element to split out, one constructs the Xmlpath portion to include a [digit/s] addition to the normal simple path. The other place we see this kind of construction is in Tables, choosing a header, for instance. It's not identical, hence "inkling"... because the header label includes a preceding comma: ,[a] for example not just the [a] .
That gives me to think (so... a "th-inkling"?) that since the phrasing similarity suggests that it is on purpose, perhaps they intend to adapt the FILTERXML() function to be a SPLIT() function someday after they resolve whatever problems they have extending the SPILL functionality to the Tables functionality.
So, a new additon to the "For what it's worth" category.
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.
The UDF in a Named Range thing, just create the thing, then when happy with it (all polished), copy the sheet to the new workbook template so the Named Range work is copied into it without error, then delete the sheet and everything new will include the function you created.
And no macro status.
They'll be even better once the LET() function is broadly available. Single name works, easier to maintain. Man... when I was a kid, those clicker toys only clicked, they didn't even usually add on an extra "toy" layer by painting them like ladybugs or whatever. Then in the '80's I saw some that did that, made them toy-ish even when not clicking them. Now LET() is coming! Life just gets better every day!
With text in column A and which element of it to return in G1:
=IF(OR($G$1<1, $G$1>COUNT( UNIQUE(IFERROR(FIND(" ",$A6,SEQUENCE(1,LEN($A6))),LEN($A6)),1) )),
"Error in which element to select.",
1 + IF($G$1=1,0,
INDEX( UNIQUE(IFERROR(FIND(" ",$A6,SEQUENCE(1,LEN($A6))),LEN($A6)),1), $G$1-1)),
-1 + INDEX( UNIQUE(IFERROR(FIND(" ",$A6,SEQUENCE(1,LEN($A6))),LEN($A6)),1), $G$1) - IF($G$1=1,0, INDEX( UNIQUE(IFERROR(FIND(" ",$A6,SEQUENCE(1,LEN($A6))),LEN($A6)),1), $G$1-1))
As I have it for actual use, I put a large part of the four basic elements into a Named Range so it looks cleaner, but I will probably put it all in (you can do what is effectively a UDF inside a Named Range so I can pass it the parameter of the cell (G1 used here) that tells it what element to obtain.
It is hardcoded for a "space" as the delimiter, but that can easily be upgraded to allow one to be selected. It will return anything between the spaces which is good and bad, but that's the general effect everyone faces: how do you get it to return precisely what's there, but also, gosh, expect it to know you wanted "pig" not "pig,"? However, it is set to drop the absolute last character of a string, expecting a sentence and therefore ending punctuation (I set this one up to operate on text, so... not as generalized as it could be, yet), and not desiring the ending punctuation to be included. Easily modified if that IS desired, or it's used on text for which that condition isn't the case.
Excel Wizard commented
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 :-)
The following can find a desired instance number in a delimted data cell:
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 n-th (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 multi-character 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.
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.
به نمایندگی از بِچا آبودان:
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