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.
Splitting a name into first name last name in Excel 2007

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





Posted in Spreadsheets