If you have full names in a single column and want to extract just the last name, Excel provides various methods to do so. Here’s a tutorial to help you get it done.
This method works when the full name is in the format "First Last."
Identify the Column:
A1
.Insert a New Column for Last Name:
Use the Formula:
=RIGHT(A1,LEN(A1)-FIND(" ",A1))
FIND(" ",A1)
locates the space between the first and last names.LEN(A1)
calculates the total length of the name.RIGHT
extracts the text after the space.Press Enter:
Copy the Formula Down:
The TEXTAFTER function (available in Excel 365 and Excel 2021) simplifies extracting text after a specific character.
Insert a New Column for Last Name:
Use the Formula:
=TEXTAFTER(A1, " ")
" "
specifies the space as the delimiter.Press Enter:
Copy the Formula Down:
Flash Fill is a quick and easy way to extract last names without formulas.
Type the Last Name:
Activate Flash Fill:
Excel Fills the Column:
CertFusion also offers a versatile tool for extracting name components, including last names, from complex datasets.
Access the Tool:
Upload Your Excel File:
Choose Detection Options:
Process the Data:
Download the Result:
Discover more insights and stories that might interest you