Jan 24 2009

Separate Full Name into First & Last Name

Here are two easy formulas that will separate a cell that contains a full name (first and last name format) into 2 cells – one containing the first name and the other containing the last name.  To do this we are going to make use of the left, right, len & find formulas.



Extract First Name from Full Name

To extract the first name from cell A1, which contains the full name,  we use the formula as follows:

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

The way this work is the find formula returns the number of characters where it finds the first space – we then subtract one from that formula because we want the location of the last non-space character.  The Left formula then takes the first characters equal to the number returned by FIND()-1.

Extract Last Name from Full Name

To get the last name from the full name, we are going to piggyback off the original formula from above.  Using the following formula will extract the last name from full name:

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

Let’s take a look at this formula.  The first thing that is done is we find the total length of the cell contents of A1.  Next, the character count of the location of the first space is subtracted from the total length of A1.  This should leave us with the length of the last name.  The final step is to take the Right formula and select the number of characters in the length of the last name of cell A1.

Maybe taking a look at the examples will make it clearer.

Example

Cell A1 contains the full name “John Smith”

First Name Formula: =LEFT(A1,FIND(” “,A1)-1) – this will return “John”.

Last Name Formula: =RIGHT(A1,LEN(A1)-FIND(” “,A1)) – this will return “Smith”.

Easy right?  Be careful though, these formulas will only work in the format of Full Name = First Name + Space + Last Name.  Lets take a look at what happens if we have the format – Full Name = First Name + Space + Middle Name + Space + Last Name.

Full Name = “John Daniel Smith”

First Name Formula: =LEFT(A1,FIND(” “,A1)-1) – this will return “John”.

Last Name Formula: =RIGHT(A1,LEN(A1)-FIND(” “,A1)) – this will return “Daniel Smith”.

As you can see, the first name formula works correctly.  However, the last name formula will return both the middle and last name.  We will return to this in a later post to explore how to get all three names out of the Full Name in that format.

Random Posts

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          
TAGS:

4 Comments on this post

Trackbacks

  1. Using Text to Columns to Separate Data | Excel Hints wrote:

    [...] post can be seen as a follow up to an earlier post, Separate Full Name into First & Last Name.  Excel provides a very easy to use tool to separate delimited data into multiple columns.  All [...]

    January 29th, 2010 at 1:07 am
  1. vinay said:

    I have a name called “Sanjay Kumar Gupta”…i want to get firstname and middlename in one column and lastname in second column. can anybody help in getting it

    Thanks in advance!!

    July 8th, 2011 at 2:24 am
  2. Vinicius Camara said:

    First of all, congratulations. This post is awesome!
    In my excel works but I needed change function separators:
    =LEFT(A2;FIND(” “; A2)-1)
    =RIGHT(A2;LEN(A2)-FIND(” “;A2))

    Thank you.

    August 3rd, 2011 at 11:24 am
  3. Varun said:

    If we use =LEFT(A2;LEN(A2)-FIND(” “;A2)) to get the first name then why dont get the entire first name.
    i am not an excel person so would be great if the similar formula is used for both left and right words.

    August 4th, 2011 at 8:46 am

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives