How to Extract Last Names from Full Names in Excel Documents

Sometimes you might be left with a huge list of full names and you might be required to extract the last names from this list of full names. Doing this manually is sure to drive any sane person go insane. So is there a way how you can do this automically? Well, we are here with 2 ways, not just one!

Dive into the article to learn how you can easily extract last names from a list of full names in Microsoft Excel.

Example Scenario

Let’s say you have 2 columns in your Excel sheet. One named Customer Full Name, already populated with a list of full names and another one named Last Name which is to be populated with the last names. The last names are to be extracted from the full name column. Let’s see how this task can be achieved through 2 different solutions detailed in this article.

Solution 1: Using Find and Replace Method

Step 1: Firstly, select all the data in the first column and hit the keys CTRL + C to copy all the data.


1 Copy Data Min


Step 2: Now click on the first cell of the column where you want the last names to be extracted to.


2 Click First Cell Min


Step 3: As next, hit the keys CTRL + V at the same time to paste all the copied data.


3 Data Replaced Min


Step 4: Now we are going to use the Find and Replace method to extract only the last names from the first name.

Press the keys CTRL + H simultaneously to launch the Find and Replace window. You will automatically be under the Replace tab.

Now, in the Find what field, enter * and then a space character. This means in each cell, find everything that comes before the last space character.

In the Replace with field, type nothing, simply leave the field empty. Which means that whaetever is found before the last space character in each cell has to be replaced with nothing, i.e., effectively it extracts the last word in the cell.

That’s it. Hit the Replace All button once you are all set.


4 Replace All Min


Step 5: You will now get a dialog box from Microsoft Excel saying that all the replacements have been successfully made. Just hit the OK button to close this window.

That’s it, the last names are all beautifully extracted.


5 Replacement Done Min


Solution 2: Using Pattern Identification Method

This method tells Excel of a pattern that you want Excel to identify and replicate. This method is also known as the Flash Fill method.

Step 1: In the first 2 cells of the Last Name column, simply type in the last names manually. This is to teach Excel of the pattern that you are looking for.


6 Type Pattern Min


Step 2: As next, select the range of cells in the column that you want to be auto-populated. Remember to include the manually filled in cells as well.


7 Select Cells Min


Step 3: Press the keys CTRL + E together next and you will see the magic.

Excel identifies the pattern and applies it to the whole selected range of cells.


8 Flash Fill Min


Stay tuned for more tricks, tips and how-to artciles.