Excel: How to Extract Last Name from Full Name


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: How to Extract First Name from Full Name in Excel

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.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Count Frequency of Text in Excel
How to Check if Cell Contains Text from List in Excel
How to Calculate Average If Cell Contains Text in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *