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)?

Ostap

In what way do you want the words to be returned? Excel can not store an array in a single cell. So what should it be? – Matronna

Answer 1

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

Andri

Thank you, that's just what I was after – Ostap

Why oh why don't they add some decent string parsing functions to Excel!?!? – Apophasis

@Apophasis because Excel is primarily made for accountants and there is no elegant way to override the assumption that an accountant is driving the application. There needs to be an Excel for programmers. – Sailer

Answer 2

The following returns the first word in cell A1 when separated by a space (works in Excel 2003):

=LEFT(A1, SEARCH(" ",A1,1))

Way

Yes, but if there is no space in the cell, it will just return an error! – Ostap

=LEFT(A1, SEARCH(" ",CONCATENATE(A1," "),1)) – Grist

Answer 3

=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))

Inessive

Obviously you can change A3 to which ever cell it is your trying to evaluate, James. – Inessive

Didn't quite do what I was after, but +1 because it was helpful elsewhere – Ostap

Answer 4

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.

Diplegia

Answer 5

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 > -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","/")

Erena

I usually add a call to Transpose If Application.Caller.Rows.Count > Application.Caller.Columns.Count at the end in case the formula is entered down the column rather than across – Sufferance

Answer 6

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

Melisent

Answer 7

EDIT 7-10-2022:

New functions have been roled out in the current channels including TEXTSPLIT(). Use the 2nd and/or 3rd parameter to split a text on 1+ delimiters (array or range) into columns and rows.

I'll leave the older answer intact for users of older versions of Excel.

---

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("<t><s>"&SUBSTITUTE(A1,"|","</s><s>")&"</s></t>","//s")

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.

Harpoon

Answer 8

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.

Stringent