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