How to split a name using MS Excel 2007

For the purposes of data import / export it is often necessary to split a name into the first name and last name, for example when importing contact data into a CRM system. So how do you split a name into first name and last name using MS Excel 2007?

The following tutorial shows you how to split the name into 2 columns. In the example below we have the full name in Cell A2. The first name and last name are separated by the space character. We can split out the first name as follows =LEFT(A2,FIND(" ",A2)-1)

We can split out the last name as follows =RIGHT(A2,LEN(A2)-FIND(" ",A2))

How does it work?

Extracting the first name from a full name in Excel

1. Starting from the left, count the number of characters to the first space using the FIND() function:

=FIND(" ", A2)

2. Subtract 1 from that value to get the length of the first name:

=FIND(" ",A2)-1

3. Use the LEFT() function to extract that number of characters, starting from the left, to get the first name:

=LEFT(A2,FIND(" ",A2)-1)

Extracting the surname from a full name in Excel

1. Starting from the left, count the number of characters to the first space using the FIND() function:

=FIND(" ", A2)

2. Using the LEN() function, count the total number of characters in the surname:

=LEN(A2)

3. Calculate the length of the surname by subtracting the first result from the second:

=LEN(A2)-FIND(" ",A2)

4. Use the RIGHT() function to extract the surname from the full name, making use of the surname length calculated above:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

I hope this helps.

Date: 27th June 2013

Author: Duncan Gillingwater