Get first name and last name in Excel | Split full names

This is a tutorial on how to split first name and last name with a function in Microsoft Excel. We use function LEFT to get the name on the left side (usually first name) and RIGHT to get the name on the right side (usually last name).



Syntax


LEFT(source text; number of characters to return)
RIGHT(source text; number of characters to return)

Example 

 

First name


If we have a full name (Clint Dempsey for example) in cell A1, we can get the first name with:

=LEFT(A1;SEARCH(" ";A1)-1)

And so we have extracted "Clint".

Explanation

 

A1 is the source text, but we have to use the SEARCH function to figure out how many characters there are between the beginning and the space " " between names. SEARCH function returns the number of characters WITH the space, so we have to take out one character from the outcome, hence the -1. If didn't do this, we'd have "Clint ", with the space in the end.

Last name


To get the last name we do:

=RIGHT(A2;LEN(A2)-SEARCH(" ";A2))

And so we have extracted "Dempsey".

Explanation

 

A1 is the source text, but we have to use the SEARCH function to figure out how many characters there are in the first name. SEARCH gives us the number of characters between the beginning and the space " " between names (SEARCH function returns the number of characters WITH the space).

We use the LEN function to count the character length of the full name and from this result we deduct the SEARCH function result to have the rigth number of characters returned FROM THE RIGHT.

2 comments:

  1. Clued-up post! Your accepted wisdom is great. Thanks for keep me notify. For more information I will be in touch.
    financial modeling expert

    ReplyDelete
  2. AWS Training in Bangalore - Live Online & Classroom
    myTectra Amazon Web Services (AWS) certification training helps you to gain real time hands on experience on AWS. myTectra offers AWS training in Bangalore using classroom and AWS Online Training globally. AWS Training at myTectra delivered by the experienced professional who has atleast 4 years of relavent AWS experince and overall 8-15 years of IT experience. myTectra Offers AWS Training since 2013 and retained the positions of Top AWS Training Company in Bangalore and India.


    IOT Training in Bangalore - Live Online & Classroom
    IOT Training course observes iot as the platform for networking of different devices on the internet and their inter related communication. Reading data through the sensors and processing it with applications sitting in the cloud and thereafter passing the processed data to generate different kind of output is the motive of the complete curricula. Students are made to understand the type of input devices and communications among the devices in a wireless media.

    ReplyDelete