Sheet-side 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/extract-words.htm.
Kenneth Barber
shared this idea
8 comments
-
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.
-
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 -
-
And it's already supported in FormulaDesk...