Table of Contents
How Do I Get The Last Name From A Full Name in an Excel Column?
To extract the last name from a full name in an Excel cell, you can use the following steps:
1. Assuming the full name is in cell A1, you can use the following formula to extract the last name:
=RIGHT(A1,LEN(A1)-FIND(” “,A1))
2. Press Enter to get the last name extracted from the full name.
This formula works by finding the position of the space in the full name using the FIND function, and then using the RIGHT function to extract the text to the right of the space, which represents the last name.
How To Separate The Surname From a Full Name in Excel, According to Quora:
This answer was suggested by J.L. Wiggins.
You can use Excel’s “Text to Columns” button under the Data tab. You can also use Flash Fill (I believe someone has provided details for how to do that). If this is a one-shot data cleanup, I would use one or the other of those methods. If you need a programmatic way, it is a bit more convoluted but try this:
Start with your table of names. I started with the following table of obscure names:
The formula for finding the given or first name using text functions is:
=LEFT([@[Full Name]], FIND(” “,[@[Full Name]]))
and the formula for finding the surname (family name) would be:
=RIGHT([@[Full Name]], (LEN([@[Full Name]])-FIND(” “, [@[Full Name]])))
Remember, this is a table so it uses table references rather than cell references. If you use range or cell references the formulas in the first two top cells would look like this:
=LEFT($A2, FIND(” “, $A2))
And this:
=RIGHT($A2, (LEN($A2)-FIND(” “, $A2)))
The final table looks like this:
Essentially, you use the FIND function to locate the space, the LEN function to get the length of each full name, and then the LEFT and RIGHT functions to “snip out” the strings using those numbers.
