Table of Contents
The process of extracting the last name from a full name in Excel involves using the built-in functions and formulas to manipulate the data. This can be achieved by using the “RIGHT” function to extract the last name from the full name, followed by using the “FIND” function to locate the position of the last name within the full name. By combining these functions, the user can accurately extract the last name from a full name in Excel. This method is useful for organizing and sorting data, as well as creating personalized reports and documents.
Excel: 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:
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.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I extract the last name from a full name in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-extract-the-last-name-from-a-full-name-in-excel/
stats writer. "How can I extract the last name from a full name in Excel?." PSYCHOLOGICAL SCALES, 26 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-extract-the-last-name-from-a-full-name-in-excel/.
stats writer. "How can I extract the last name from a full name in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-extract-the-last-name-from-a-full-name-in-excel/.
stats writer (2024) 'How can I extract the last name from a full name in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-extract-the-last-name-from-a-full-name-in-excel/.
[1] stats writer, "How can I extract the last name from a full name in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I extract the last name from a full name in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.