Feedback by UserVoice

 

How can we improve Excel for Windows (Desktop Application)?

← Excel for Windows (Desktop Application)

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.

74 votes
Sign in
(thinking…)
Sign in with: Facebook Google
Forgot password?
Create a password
Signed in as (Sign out)
Close
Close

We’ll send you updates on this idea

Kenneth Barber shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

20 comments

Sign in
(thinking…)
Sign in with: Facebook Google
Forgot password?
Create a password
Signed in as (Sign out)
Close
Close
Submitting...
An error occurred while saving the comment
  • Sergei Baklan commented  ·   ·  Flag as inappropriate

    That could be like
    =TRANSPOSE(FILTERXML("<r><n>" & SUBSTITUTE(A1,",","</n><n>") & "</n></r>", "//n"))
    If extract, for example, 2nd word
    =FILTERXML("<r><n>" & SUBSTITUTE(A1,",","</n><n>") & "</n></r>", "//n[2]")

  • Leif Hille commented  ·   ·  Flag as inappropriate

    Kenneth, actually my “Sum_Fields2D - returns a sum of an array along a specified dimension” takes a text matrix input and sums elementwise the values along the specified dimension. So MxN array summed on 1st dimension provides 1xN array of values (each is a sum of the M values in that row) and conversely summing on second dimension provides Mx1 result vector of sums of the N values in each column.

    This is super helpful in aggregating arbitrary sized text matrices.

  • Kenneth Barber commented  ·   ·  Flag as inappropriate

    In response to Fabio, your formula extracts only the last word of a string. It does not split the string, at each occurrence of the delimiter, into an array of words. It is still helpful, but we're going after something more generalized.

    In response to Leif, it sounds like your Join function is like TEXTJOIN and createEyeMatrix is like MUNIT. Reverse can be simulated with CONCAT(MID(<string>,LEN(<string>)-SEQUENCE(LEN(<string>),,0),1)). Sum_Fields2D can be simulated with SUM(INDEX()), using 0 as an index to represent a whole row or whole column. Really, we just need Split and Reverse.

  • Leif Hille commented  ·   ·  Flag as inappropriate

    I have stock set of VBA excel sheet string & field-management functions which I use constantly. It would be helpful to have these hard-coded into excel.

    Split - splits a string into 1D Array (delimiter specified)
    Split2d - splits a string into a 2D array for matrices (delimiters specified)
    Get_Nth_Field - picks out a Nth value in a 1D Array String (delimiter specified)
    Sum_Fields2D - returns a sum of an array along a specified dimension
    Join - join arrays to string - works on 2D and 1D arrays (delimiters specified)
    Reverse - reverses a string
    RepeatStr - Repeats a specified string N times ==> this function exists now: REPT
    createEyeMatrix - useful for creating numerical matrix with 1s on diagonal

  • Fabio Baldini commented  ·   ·  Flag as inappropriate

    Hi Kenneth I used this formule - =TRIM(RIGHT(SUBSTITUTE(A2;" ";REPT(" ";LEN(A2)));LEN(A2))) Best Regards

  • Fernando Fernandes commented  ·   ·  Flag as inappropriate

    I agree that a split function to return an array based on a delimiter, to be used in array formulas, would be very much welcome to Excel interface.I'm forwarding this link to a bunch of people...

  • Sergei Baklan commented  ·   ·  Flag as inappropriate

    like =SPLIT(<text>,<delimiter>,[<splitby>],[<splitto>)
    where
    splitby
    -1 by first delimiter
    0 by each delimiter (default)
    1 by right delimiter

    splitto
    TRUE to columns (default)
    FALSE to rows

  • Kenneth Barber commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    I'd love to have a TextSplit function in Excel. I'd use it heavily, and my Excel files would be much cleaner.

  • Charles commented  ·   ·  Flag as inappropriate

    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

Excel for Windows (Desktop Application): Formulas and Functions

Categories

Feedback and Knowledge Base

(thinking…)