Go Back

How to Get the Last Name from Name

We can extract the last name from the full name of an individual in a cell by using a combination of the following functions: RIGHT, LEN, FIND, and SUBSTITUTE. The steps below will walk through the process.

Figure 1: How to Get Last Name from Name

Formula

=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",LEN(name)-LEN(SUBSTITUTE(name," ","")))))

Note that the name is a full name that contains a blank space between the first name and the last name.

Setting up the Data

  • We will set up the data by inserting the full names in Column A.
  • Column B will contain the First name and Column C will contain the Last name

Figure 2: Setting up the Data to Get the Last Name from Name

Get the Last Name from Name

  • We will click on Cell C4
  • We will insert the formula below

=RIGHT(A4,LEN(A4)-FIND("*",SUBSTITUTE(A4," ","*",LEN(A4)-LEN(SUBSTITUTE(A4," ","")))))

  • We will press the enter key

Figure 3: Inputting the formula to Get the Last Name from Full Name in Cell A4

  • We will use the drop-down feature to drag down and get the last names of the remaining cells

Figure 4: Result of the Last Name from Full Names

Get the First Name from Name

We can get the first names by changing RIGHT in the formula to LEFT.

We will input the formula below into Cell B4, press enter and drag down to get the values for the remaining cells.

Figure 5: Result of the First Name from Full Names

Explanation

Formula: =RIGHT(A4,LEN(A4)-FIND("*",SUBSTITUTE(A4," ","*",LEN(A4)-LEN(SUBSTITUTE(A4," ","")))))

The RIGHT function is used to extract characters starting from the right. The formula uses an asterisk (“*”) to replace the last space in the full name. The FIND function is used to locate the exact position of the asterisk. This enables the RIGHT function to know where to stop extracting values from the right.

The SUBSTITUTE function performs the job of replacing the space in the name with the asterisk. This is based on the section of the formula below:

=SUBSTITUTE(A4," ","*",LEN(A4)-LEN(SUBSTITUTE(A4," ","")))))

The SUBSTITUTE function below replaces the space in A4 with no space. From this part of the function, we will observe that the asterisk is absent.

=SUBSTITUTE(A4," ","") = EmilyKelvin

The LEN function below calculates the number of spaces. This is indicated by the inverted commas which pulls the texts in the cell together.

=LEN(A4)-LEN(SUBSTITUTE(A4," ","")))))

LEN A4 is the total number of characters including the space in Cell A4. That is 12.

LEN(SUBSTITUTE(A4," ",""))))) is the total number of characters excluding the space in Cell A4. That is 11.

As a result, the number of spaces in the name in Cell A4 is 1.

The SUBSTITUTE function below can now be like this IN THE RIGHT:

=SUBSTITUTE(A4," ","*",  = SUBSTITUTE(A4," ","*",1)

The SUBSTITUTE Function establishes that there is only one space and identifies the position of the space.

The FIND function locates the asterisk and returns the position of the asterisk in the name.

=FIND(“*”,Emily*Kelvin)

The result is 6 because “*” is in the 6th position. It is then subtracted by the length of the name by:

=LEN(A4)-6

12 – 6 = 6

The number 6 is then used by RIGHT function:

=RIGHT(A4,6)

This returns the result as Kelvin.

Note

  • Inconsistent spacing poses as a common error that can be fixed with the TRIM function

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I want to get the first name, last name from the full name of students. And order them by alphabet
Solved by V. A. in 16 mins
I want to change the order of a person's name in a cell from: 'first name last name' to 'last name,first name'
Solved by O. J. in 13 mins
I need to split one column into first name and last name. I was able to get help with the last name but now I need help with the first name.
Solved by V. Q. in 13 mins

Leave a Comment

avatar