Splitting text in Excel

Splitting a cell into words

Something that has come up several times today already. If I have a cell (say A2) in excel that contains a name (made up of firstname and lastname), how can I extract firstname and lastname into two separate cells?

 

If the name in A2 is Carl Sprake then to split it into two words we need to find where the space is. We can do this using the FIND function

 

=FIND(” “,A2)

 

This will return the number 5.

 

So we can extract the first word using the LEFT function:

 

=LEFT(A2,5)

 

This will work for my name, but for my friend Andrew it will only return Andre. We can therefore combine the two functions into one:

 

=LEFT(A2, FIND(” “,A2)-1)

 

The -1 is because the Find function gives the number of the space, whereas what we really want is the letters that make up the first name, which is one less than the position of the space.

 

To extract the last name we need to know the total length of the string. We can use the LEN function for this.

=LEN(A2)

 

This returns a value of 11.

 

So, knowing how long the string is in total, and where the space is, we can use the RIGHT function:

 

=RIGHT(A2, LEN(A2)-FIND(” “,A2))