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

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? — Tomalak
– Tomalak, Commented Jun 23, 2009 at 8:35

Robert Mearns · Accepted Answer · 2009-06-23 15:28:29Z

24

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

answered Jun 23, 2009 at 15:28

Robert Mearns

12.1k3 gold badges41 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

a_m0d Over a year ago

Thank you, that's just what I was after

Rory Over a year ago

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

dreftymac Over a year ago

@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.

RobS · Accepted Answer · 2009-06-23 08:31:25Z

10

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

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

answered Jun 23, 2009 at 8:31

RobS

3,86729 silver badges34 bronze badges

2 Comments

a_m0d Over a year ago

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

flash Over a year ago

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

James · Accepted Answer · 2009-06-23 09:00:16Z

10

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

edited Jun 23, 2009 at 9:00

answered Jun 23, 2009 at 8:43

James

82.4k19 gold badges172 silver badges245 bronze badges

2 Comments

James Over a year ago

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

a_m0d Over a year ago

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

Community · Accepted Answer · 2011-10-05 19:35:29Z

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.

edited Oct 5, 2011 at 19:35

CommunityBot

11 silver badge

answered Jun 23, 2009 at 15:40

Carl Manaster

40.5k17 gold badges109 silver badges158 bronze badges

Comments

S Meaden · Accepted Answer · 2017-06-27 22:47:40Z

3

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

answered Jun 27, 2017 at 22:47

S Meaden

8,3203 gold badges39 silver badges74 bronze badges

1 Comment

airstrike Over a year ago

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

Toffix · Accepted Answer · 2014-09-12 10:41:07Z

2

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

answered Sep 12, 2014 at 10:41

Toffix

513 bronze badges

Comments

JvdV · Accepted Answer · 2022-10-07 11:09:17Z

2

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.

edited Oct 7, 2022 at 11:09

answered Jul 21, 2020 at 20:35

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Comments

user4196835 · Accepted Answer · 2014-10-30 04:34:53Z

1

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.

answered Oct 30, 2014 at 4:34

user4196835

111 bronze badge

Collectives™ on Stack Overflow

Simulate string split function in Excel formula

8 Answers 8

3 Comments

2 Comments

2 Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

3 Comments

2 Comments

2 Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related