How to Extract Last Names from Full Names in Excel: Two Simple Solutions

Do you ever find yourself with a long list of full names in an Excel document and need to extract only the last names? Doing this task manually can be frustrating and time-consuming. However, there are two simple ways to automate this process that we will share with you.

Let's imagine you have two columns in your Excel sheet. One is labeled "Customer Full Name," which contains a list of full names, and the other is labeled "Last Name," which needs to be populated with the extracted last names.

Solution 1: Using Find and Replace Method

Select all the data in the "Customer Full Name" column and copy it using the keyboard shortcut CTRL + C.

Click on the first cell of the "Last Name" column where you want the last names to be extracted.

Paste the copied data into the "Last Name" column using the keyboard shortcut CTRL + V.

Use the Find and Replace method to extract only the last names from the full name.

Press the keyboard shortcut CTRL + H to launch the Find and Replace window, and select the Replace tab.

In the "Find what" field, enter * and then a space character. This tells Excel to find everything in each cell before the last space character.

In the "Replace with" field, leave it empty. This effectively extracts the last word in the cell.

Click the "Replace All" button once you're ready.

You will receive a dialog box from Excel, indicating that all the replacements have been successfully made. Click the "OK" button to close this window.

Now, all the last names have been extracted and appear beautifully in the "Last Name" column.

Solution 2: Using Pattern Identification Method

This method, also known as the Flash Fill method, teaches Excel to identify a pattern and apply it automatically.

Type the first two last names manually in the "Last Name" column, next to the corresponding full names.

Select the range of cells in the "Last Name" column that needs to be auto-populated, including the manually filled cells.

Press the keyboard shortcut CTRL + E, and Excel will automatically identify the pattern and apply it to the whole selected range of cells.

That's it! You have now learned how to extract last names from full names in an Excel document using two different methods. Stay tuned for more tips, tricks, and how-to articles.