Extract Last Word in Excel

As you work with data in Excel, you might need to extract the last word in a cell, for example:

  • Extract the last name: If you have a column with full names (e.g., “Elizabeth Clinton,” “Prince Smith,” “Prof. George W. Jefferson”), you might want to extract and isolate the last names and store them in a separate column.
  • Extract ZIP code from the address: If you have a column with addresses (such as 2554 Libby Street Pomona, CA 91766), and you want to extract the ZIP code from this address.

This tutorial shows five techniques for extracting the last word in a cell in Excel.

Method #1: Using Flash Fill to Extract the Last Word in a Cell

The Flash Fill feature in Excel automatically fills data in a column based on patterns detected in the adjacent column. You can use this feature to extract the last word in a cell in Excel.

Suppose we have the following dataset of full names:

Dataset of full name

Notice that the full names in column A display irregular patterns, with some including prefixes such as Prof. or Mr., while others have middle names.

We want to use Flash Fill to extract the last names from the full names in column A and store them in column B.

Below are the steps that will do this:

  1. Select cell B2 and type in “Jefferson,” which is the last name of the full name in cell A2.
  2. Select cell B3 and type in “Clinton,” which is the last name of the full name in cell A3.
  3. Select cell B4 and type in “Lakeland,” which is the last name of the full name in cell A4. Notice that as you begin to type this third entry, Flash Fill detects the pattern you want it to follow and gives you suggestions in grey for the following entries:
Flash Fill suggestions
  1. If the suggestions are correct, press “Enter,” and the last names will be filled in automatically.  
press Enter to fill the suggestions

If the Flash Fill suggestions are incorrect, continue with the following steps:

  1. Ignore the suggestions and finish typing in the third entry.
  2. Select cell B5 which is immediately below the third entry:
Shortcut to run Flash fill
  1. Press “Ctrl + E,” the shortcut for Flash Fill. Alternatively, on the “Home” tab, open the “Fill” drop-down on the “Editing” group and choose “Flash Fill.”
click on the flash fill icon

The rest of the last names are filled in automatically:

press Enter to fill the suggestions

Note: Flash Fill may produce inaccurate outcomes in some cases, particularly when the source data has intricate or irregular patterns. Therefore, we recommend double-checking the Flash Fill results and manually fixing any mistakes.

It’s important to note that Flash Fill is not dynamic. If there are any changes in the source data, you’ll need to rerun Flash Fill to get updated results.

Also read: How to Extract First Name in Excel

Method #2: Using Find and Replace to Extract the Last Word in a Cell

Excel’s Find and Replace feature makes it easy to search for specific data in a worksheet or selected range of cells and replace it with alternative content.

We can use this feature to extract the last word in a cell in Excel.

Let’s consider the following dataset containing full names with irregular patterns:

Dataset of full name

We want to use the Find and Replace feature to extract the last names from the full names in column A and store them in column B.

We use the below steps:

  1. Copy the names in column A and paste them in column B to ensure the Find and Replace feature does not destroy the original data.
Copy names in column B
  1. Select the names in column B.
Select all then names in column B
  1. Press “Ctrl + H” to open the “Find and Replace” dialog box. Alternatively, on the “Home” tab, open the “Find & Select” drop-down on the “Editing” group and select “Replace.”
Click on the Replace option
  1. On the “Find and Replace ” dialog box, enter an asterisk (*) followed by a space on the “Find what” drop-down, leave the “Replace with” drop-down blank, and click “Replace All.”
enter an asterisk followed by space

Note: The asterisk (*) symbol is a wild character representing any number of characters. Using the asterisk followed by a space, we instructed the “Replace and Find” feature to locate the last space character in every selected text string and replace said space character with the preceding substrings with empty strings.

Excel informs us of the number of replacements done:

prompt showing how many replacements were done

The process has the effect of leaving only the last names in column B as seen below:

only last name is left

Please note that for this method to work, the text strings must not have any trailing spaces; otherwise, “Find and Replace” will wipe out all the text strings. Before using this method, you can use the TRIM function to remove trailing spaces from the dataset.

This method of applying the “Find and Replace” dialog box is not dynamic, and therefore if there is a change in the source data, you must reapply the process to generate updated results.

Also read: Extract ZIP Code from Address in Excel

Method #3: Using Power Query to Extract the Last Word in a Cell

Power Query allows us to connect to, transform, and combine data from multiple sources, making it easier to analyze and work with large data sets.

We can apply this feature in extracting the last word in a cell in Excel.

Assume we have the following dataset containing full names with irregular patterns:

Names dataset

We want to use Power Query to extract the last names from the full names and store them in a different column.

We use the following steps:

  1. Select any cell in the dataset and press “Ctrl  + T” to convert it to an Excel table.
  2. Click “OK” on the “Create Table” dialog box.
Convert range to an excel table

The dataset is converted to a table:

Data converted to Excel table

Note: A great advantage of converting the dataset into a table before utilizing Power Query is that it automatically expands to include any new data added to the table and incorporates it into subsequent operations. This step makes it much easier to refresh the data in Power Query and keep it current.

  1. Select any cell in the table, open the “Data” tab on the Ribbon, and click the “From Table/Range” command button on the “Get & Transform Data” group.
Image23
  1. Open the “Add Column” tab on the Power Query Editor and click the “Duplicate Column” button.
Click on Duplicate column

This step creates a duplicate column that we can transform, preserving the original data.

  1. Change the header of  the duplicate column to “Last Name.”
Name the column Last name
  1. Click the header of the duplicate column to select it. On the “Home” tab, open the “Split Column” drop-down on the “Transform” group and select “By Delimiter.”
Click on By Delimiter
  1. On the “Split Column by Delimiter” dialog box, select “Space” on the “Select or enter delimiter” drop-down, select the “Right-most delimiter” option, and click “OK.”
select right most delimiter
  1. Select the split column containing the other names preceding the last name. On the “Home” tab, open the “Remove Columns” drop-down on the “Manage Columns” group and select “Remove Columns.”
Remove the column

Remove the suffix  attached to the header of the remaining split column:

Remove the suffix  attached to the header
  1. Click the “Close & Load” command button.
Click on Close and load

Finally, a new worksheet is created, and the transformed table containing the last names is loaded onto it:

result shown in separate table

As you have observed, this approach is lengthy and may not be ideal if you only need to extract last names occasionally.

However, this method is recommended if your job entails frequently extracting last names from datasets.

When you get a new dataset, there is no need to redo the entire process. Just link the query to the dataset, refresh the query, and the task is completed.

Also read: Switch First and Last Name with Comma in Excel (Reverse Names)

Method #4: Use TEXTAFTER Function to Extract the Last Word in a Cell

We can use the TEXTAFTER function, which returns text after delimiting characters to extract the last word in a cell in Excel.

Note: The TEXTAFTER function is only available in Excel 365 on the Insider channel.

Assume we have the following dataset containing full names with irregular patterns:

Dataset of full name

We want to use the TEXTAFTER function to extract the last names from the full names in column A and display them in column B.

We use the following steps:

  1. Select cell B2 and enter the formula below:
=TEXTAFTER(A2," ",-1)
TEXTAFTER function
  1. Drag or double-click the fill handle feature in cell B2 to copy the formula down the column.
Last name as result of the formula

Explanation of the formula

=TEXTAFTER(A2,” “,-1)

The formula extracts the text after the last space character in cell A2.

The “-1” argument specifies that the function should start searching for the delimiter from the end of the text string.

Also read: How to Extract Text After Space Character in Excel?

Method #5: Using TRIM + RIGHT,+ SUBSTITUTE Formula to Extract the Last Word in a Cell

Suppose we have the following dataset containing full names with irregular patterns:

Dataset of full name

We want to extract the last names from the full names in column A and store them in column B.

We proceed as follows:

  1. Select cell B2 and type in the following formula:
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))
RIGHT formula to extract last word
  1. Drag or double-click the fill handle in cell B2 to copy the formula down the column.
Last name as result of the formula

Explanation of the Formula

=TRIM(RIGHT(SUBSTITUTE(A2,” “,REPT(” “,100)),100))

This formula extracts the last word from a text string in cell A2, removing any leading or trailing spaces.

Here’s a breakdown of the formula:

  1. ‘SUBSTITUTE(A2,” “,REPT(” “,100))’: This part of the formula replaces each space in cell A2 with a repeated string of 100 spaces. The ‘SUBSTITUTE’ function takes three arguments: the text to be replaced (‘A2’), the old text to be replaced (” ” which represents a single space), and the new text to replace the old text (‘REPT(” “,100)’ which repeats a space 100 times). This step generates a new string by replacing space characters with a lengthy sequence of spaces.
  1. ‘RIGHT(…, 100)’: The ‘RIGHT’ function extracts a specified number of characters from the right side of a text string. In this case, it takes the modified string from the previous step as its argument and extracts the rightmost 100 characters from it. This ensures we capture the last word in the text string and any leading or trailing spaces.
  1. TRIM(…)’: The ‘TRIM’ function takes the result from the previous step and removes any leading or trailing spaces from a text string, leaving only the last word.

This tutorial showed five techniques for extracting the last word from a cell in Excel. We hope you found the tutorial helpful.

Other articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment