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.
Leave a Comment