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

Answer 1

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

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

Answer 2

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

Answer 3

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

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, it turns out isn't valid Excel; it's mixing IF and ISERROR...

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.