Simulate string split function in Excel formula
Solution 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
Solution 2
The following returns the first word in cell A1 when separated by a space (works in Excel 2003):
=LEFT(A1, SEARCH(" ",A1,1))
Solution 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))
Solution 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.
Solution 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","/")
Related videos on Youtube
a_m0d
I enjoy programming in C++, and also know C, Java, python, php and x86 asm. The various projects that I have / am working on include (these are all just for fun and to explore the capabilities of different languages / frameworks): raytracer object oriented OS in C++ 2d game engine CAS (computer algebra system) which incorporates elements of compilers / interpreters I really enjoy contributing to http://latex.codecogs.com/png.download?%5cfrac%7bStack%7d%7bFlow%7d
Updated on July 09, 2022Comments
-
a_m0d almost 2 years
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()
orSEARCH()
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)?-
Tomalak almost 15 yearsIn 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?
-
-
a_m0d almost 15 yearsYes, but if there is no space in the cell, it will just return an error!
-
James almost 15 yearsObviously you can change A3 to which ever cell it is your trying to evaluate, James.
-
a_m0d almost 15 yearsThank you, that's just what I was after
-
a_m0d almost 15 yearsDidn't quite do what I was after, but +1 because it was helpful elsewhere
-
flash over 13 years=LEFT(A1, SEARCH(" ",CONCATENATE(A1," "),1))
-
irag10 over 9 yearsWhy oh why don't they add some decent string parsing functions to Excel!?!?
-
dreftymac over 8 years@Rory 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.
-
airstrike about 4 yearsI 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