Extracting the last word from a string in Excel is like finding a key insight hidden in a mountain of data—small but important especially when working with analytics or cleaning up messy datasets.

In data analytics, details matter, and knowing how to break down text can save you much time if not hassle. If you’ve ever felt stuck trying to figure this out, don’t worry—you’re not alone.

This Microsoft Excel guide makes it easy. I’ll walk you through each method and simple steps so you can get it done faster than anyone else. Are you ready to make Excel work harder for you? Let’s jump in!

Using the Flash Fill Command

Flash Fill in Microsoft Excel is a powerful and user-friendly feature available in versions from Excel 2013 onward. It simplifies repetitive tasks by recognizing patterns in data and automatically completing them. Once you provide a couple of examples by doing the task manually, Flash Fill detects the logic and replicates it, requiring no formulas or advanced skills.

Suppose, you need to extract the last names of a few names, as shown in the above dataset. Here’s how to use the Flash Fill command.

Name the header of the adjacent blank column of the source dataset to Last Name.

Now, manually type the last names of a few entries in the new column.

Select the new column including manually edited and blank cells.

Click on the Flash Fill command in the Data Tools block of the Data tab.

Excel should replicate the steps and extract the last word instantly.

The downside of this method is compatibility issues. Flash Fill won’t be available in the Excel app if you’re not using the appropriate Excel edition, like Excel 2013 or newer.

Using the Find and Replace Tool

A universally compatible method to pull out the last word is the Find and Replace tool. It’s available in earlier and newer Excel desktop, Mac, and web apps. This method is also free from complicated functions, arguments, and programming requirements. You use the graphical user interface to isolate the last words from the source dataset.

If you use this method, you’ll be modifying the source dataset instantly. So, create a backup worksheet before proceeding with the following steps.

Select the source dataset and press Ctrl + H on the keyboard.

This will bring up the Find and Replace dialog box.

In the Find what field, enter Asterisk Space.

Make sure you keep the Replace with field empty.

Click on the Replace All button to eliminate the whole text string leaving behind the last word in each cell.

Excel will overwrite the selected dataset in a flash.

I explained the method considering that the text strings in the source dataset use the Space character as a word separator.

If the delimiter is a different character, like Comma, Semicolon, Colon, Pipe, etc., put the delimiter just after the Asterisk symbol in the Find what field.

Since the Find and Replace tool replaces the existing content of the dataset, you can get back your data using the Excel undo feature. Simply press the Ctrl + Z keys together to revert the dataset back to its original state.

Using the REGEXEXTRACT Function

The REGEXEXTRACT function is a robust feature that allows you to extract specific patterns from text strings using regular expressions. It simplifies text processing by allowing you to target and retrieve precise elements within a string, such as the last word.

You can use this function to save time and ensure accuracy when working with large datasets needing pattern-based text extraction.

Let’s say, you want to extract the product packaging size from an inventory dataset, like the one shown above.

If you wish to go with this method, select B2 and enter the following formula into the cell:

=REGEXEXTRACT(A2,"\b\w+$")

There’s no need to customize the regular expression pattern in this formula. You only need to change the input text cell range reference, which is A2.

Hit Enter to calculate the formula and get the last word in the cell A2.

Now, drag down the fill handle of the cell B2 to copy and paste the formula into the rest of the cells of column B.

You’ll quickly get the last words you’ve been looking for in a list of text strings.

A downside of this method is the REGEXEXTRACT function is available only in Excel 365 and Excel 2021. If you’re a beginner of the regular expressions pattern matching codes it can be challenging to construct the correct expressions.

Using the TEXTSPLIT Function

If you need a formula-based approach to extract the last words in Excel but don’t want to use the REGEXEXTRACT function, TEXTSPLIT is the best option for you. It helps you split the words in a text string into different columns based on a delimiter, like Space, Semi-Colon, Colon, etc.

Then, you can copy the last words column and paste it as values into a different column to get a hard-coded text rather than an array.

Select a blank cell adjacent to the source dataset, and enter the following formula:

=TEXTSPLIT(A2," ")

I considered that the word delimiter in the text strings is the Space character. If it’s a different character, like Semi-Colon, put that symbol within the double quotes in the above formula. Also, you must change the cell reference A2 according to your own dataset.

Hit Enter.

Excel will split the source text into multiple columns of single words.

Now, select all the cells in the array formula and drag the fill handle down the columns to copy and paste the formula to the rest of the cells.

Select the cell range of the last words and press Ctrl + C to copy the content.

Now, go to the adjacent column and press Ctrl + Alt + V to bring up the Paste Special dialog box.

Select Values and click OK to paste the hard-coded values for the last words previously extracted using an array formula.

Now, delete the three, four, or more columns of the TEXTSPLIT array formula.

You should now have the source dataset and the relevant last words.

Using Power Query

When importing a large dataset into Excel through Power Query for data transformation and cleaning purposes, you can use a few techniques to split the last word from a text string. Find below the two best methods you should know:

Using the Split Column Command

You can either import a large dataset from an external source or from an Excel workbook in the local PC storage.

If you choose to import an external dataset from cloud servers or databases, go to the Data tab and click on the Get Data command.

Hover over a primary data source option on the Get Data context menu, like From Database.

An overflow menu will open on the right side. There, click on the actual data source, like From MySQL Database.

You’ll be now taken through the sign-on and dataset selection process from the external data source. Follow all on-screen instructions to import the target dataset into the Power Query Editor.

To load a local dataset, like from the active worksheet, select the cell range you want to export to Power Query and click on the From Table/Range command in the Data tab.

Select OK on the Create Table dialog box to complete the dataset export process into Power Query Editor.

You can now see the target dataset inside Power Query Editor.

Create a copy of the source data column in Power Query.

Now, click on the duplicated or copied column, and go to the Split Column menu in the Text Column block of the Transform menu.

Select By Delimiter in the context menu.

Choose Space for the Select or enter delimiter field.

To extract the last word, select the Right-most delimiter option below the Split at section.

Click OK to split the text.

Power Query Editor will split the two columns isolating the last words from the input text strings.

You can now delete the unwanted column, which is Inventory Descriptions – Copy.1 in this exercise to declutter the dataset.

Now, click on the File tab and choose the Close & Load To option.

Select the Existing worksheet option in the Import Data dialog box and click OK to export the transformed dataset back to the active Excel worksheet.

That’s it! You’ve successfully extracted the last words in text strings using Power Query. You’ve also set up an automated querying that’ll allow you to quickly transform datasets you import in the future through the same process.

Using a Power Query M Formula

Use any of the previously mentioned methods to send your source dataset into the Power Query Editor tool.

Go to the Add Column menu in the Power Query interface and click on the Custom Column command.

Type in a column header into the New column name field.

Enter the following Power Query M code into the Custom column formula field.

let
    Source = [YourColumnName],
    SplitList = Text.Split(Source, " "),
    LastWord = List.Last(SplitList)
in
    LastWord

Adjust the column references within the square brackets according to the actual columns in your Power Query Editor.

Click OK to create the new column based on the formula.

You’ll create a new column containing only the last words from the column you selected as the input in the Power Query M code.

You can now follow the dataset exporting process from the previous section to get your transformed data in an Excel worksheet.

Conclusions

Now you should know how to use Excel to extract the last word after space or any other delimiter.

If you’re a basic to intermediate-level Excel user, use the simple methods explained at the beginning, like the Flash Fill and the Find and Replace tool-based approaches.

If you prefer to use formulas, you can practice the REGEXEXTRACT function and TEXTSPLIT function-based formulas.

Finally, if your intention is to transform a large dataset when importing that from an external source, you can use the two different Power Query-based techniques.

Comment below to ask a question or share your feedback.