Table of Contents
In Excel, you can extract a last name from a full name by using the RIGHT and FIND functions. The RIGHT function allows you to extract the last name from a cell, while the FIND function allows you to identify the space between the first and last name in the cell. Together, these two functions can be used to separate the last name from the full name. You can also use the LEFT and SEARCH functions to achieve the same result.
You can use the following formula in Excel to extract the last name from a full name in a cell:
=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
This particular formula extracts the last name from a full name in cell A2.
The following example shows how to use this formula in practice.
Related:
Example: Extract Last Name from Full Name in Excel
Suppose we have the following dataset in Excel that shows the number of sales by various employees at some company:
We can use the following formula to extract the last name from each employee:
=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
We can type this formula into cell C2, then drag and fill it down to the remaining cells in column C:
Column C now contains the last name of each employee in column A.
Notice that the formula works even if the full name does or does not contain a middle name.
Note: This formula works by replacing the last space in the name with an asterisk ( * ) and then uses the FIND function to locate the asterisk and extract a certain number of characters in the name from the right.