Simulate string split function in Excel formula

Question

I am trying to split a string in an excel formula  something like I can do in many programming languages  e g   string words    some text  split         The problem is that I can t be sure that there is more than one word in the cell   If I try to use the FIND   or SEARCH   functions  they return  VALUE if there is not space   Is there any easy way to split the string so that it returns the individual words  or even better  so that it returns either the first word or all the other words

User · Accepted Answer

A formula to return either the first word or all the other words    IF ISERROR FIND     TRIM A2  1   TRIM A2  MID TRIM A2  FIND     TRIM A2  1  LEN A2      Examples and results  Text                  Description                      Results                        Blank                        Space  some                  Text no space                some some text             Text with space                  text  some                 Text with leading space          some some                  Text with trailing space         some some text some text   Text with multiple spaces        text some text   Comments on Formula    The TRIM function is used to remove all leading and trailing spaces   Duplicate spacing within the text is also removed  The FIND function then finds the first space If there is no space then the trimmed text is returned Otherwise the MID function is used to return any text after the first space

User · Answer

Highlight the cell  use Dat    Text to Columns and the DELIMITER is space  Result will appear in as many columns as the split find the space

User · Answer

The following returns the first word in cell A1 when separated by a space  works in Excel 2003     LEFT A1  SEARCH     A1 1

User · Answer

IFERROR LEFT A3  FIND      A3  1    A3    This will firstly check if the cell contains a space  if it does it will return the first value from the space  otherwise it will return the cell value    Edit   Just to add to the above formula  as it stands if there is no value in the cell it would return 0  If you are looking to display a message or something to tell the user it is empty you could use the following    IF IFERROR LEFT A3  FIND      A3  1    A3  0   Empty   IFERROR LEFT A3  FIND      A3  1    A3

User · Answer

If you need the allocation to the columns only once the answer is the  Text to Columns  functionality in MS Excel   See MS help article here  http   support microsoft com kb 214261  HTH

User · Answer

These things tend to be simpler if you write them a cell at a time  breaking the lengthy formulas up into smaller ones  where you can check them along the way   You can then hide the intermediate calculations  or roll them all up into a single formula   For instance  taking James  formula    IFERROR LEFT A3  FIND      A3  1    A3    Which is only valid in Excel 2007 or later   Break it up as follows   B3   FIND      A3  C3   IF ISERROR B3  A3 LEFT A3 B3-1     It s just a little easier to work on  a chunk at a time   Once it s done  you can turn it into    IF ISERROR FIND      A3   A3 LEFT A3 FIND      A3 -1     if you so desire

User · Answer

Some great worksheet-fu in the other answers but I think they ve overlooked that you can define a user-defined function  udf  and call this from the sheet or a formula     The next problem you have is to decide either to work with a whole array or with element   For example this UDF function code  Public Function UdfSplit ByVal sText As String  Optional ByVal sDelimiter As String        Optional ByVal lIndex As Long   -1  As Variant     Dim vSplit As Variant     vSplit   VBA Split sText  sDelimiter      If lIndex  gt  -1 Then         UdfSplit   vSplit lIndex      Else         UdfSplit   vSplit     End If End Function   allows single elements with the following in one cell   UdfSplit  EUR USD      0   or one can use a blocks of cells with   UdfSplit  EUR USD

User · Answer

AFAIK the best you can do to emulate Split   is to use FILTERXML which is available from Excel 2013 onwards  not Excel Online or Mac   The syntax more or less always is   FILTERXML  quot  lt t gt  lt s gt  quot  amp SUBSTITUTE A1  quot   quot   quot  lt  s gt  lt s gt  quot   amp  quot  lt  s gt  lt  t gt  quot   quot   s quot    This would return an array to be used in other functions and would even hold up if no delimiter is found  If you want to read more about it  maybe you are interested in this post

[excel] Simulate string split function in Excel formula

Examples related to excel

Examples related to formula

Examples related to excel-formula