Function Problems

How to Extract the Last Word from Text in Excel

Algirdas Jasaitis Oct 1, 2026 868 views

Question details

The user needs a formula to extract the final word from text strings of varying lengths in Excel.

How to Extract the Last Word from Text in Excel

Product: Excel
Device & OS: not provided
Scenario: Working with text data where the last word of varying-length strings needs to be isolated and extracted.
Observed behavior: The user wants to separate the final word using functions like SUBSTITUTE, REPT, RIGHT, and TRIM, or newer dynamic array functions.

Before you start

Ensure your text strings are separated by spaces. If there are trailing spaces at the end of the text, wrapping the cell reference in the TRIM function will prevent errors.

Solution 1Recommended

Using Classic Text Functions (SUBSTITUTE, REPT, RIGHT, TRIM)

This classic method works perfectly in all versions of Excel by manipulating spaces to isolate the final word.

This approach uses a combination of built-in text functions. It replaces every space with a large block of spaces, uses RIGHT to pull the final block, and then uses TRIM to remove the excess padding.

Select the target cell

Click on an empty cell where you want the extracted last word to appear.

Enter the nested formula

Type the formula: =TRIM(RIGHT(SUBSTITUTE(A1, " ", REPT(" ", LEN(A1))), LEN(A1))) (assuming A1 contains your source text).

Apply and fill down

Press Enter to see the last word. You can then drag the fill handle down to apply this formula to the remaining rows in your dataset.

Using Classic Text Functions (SUBSTITUTE, REPT, RIGHT, TRIM)

How it works: The REPT function creates a block of spaces equal to the length of the original text. The SUBSTITUTE function inserts these spaces between every word. The RIGHT function then grabs the end of the string, ensuring it captures the entire last word along with some extra padding spaces, which are then cleaned up by the TRIM function.

Solution 2

Using Modern Functions (TEXTSPLIT and TAKE)

For newer versions of Excel, this dynamic array formula is much cleaner and easier to read.

WPS Spreadsheet Solutions

Easily Extract and Manage Text Data with WPS Spreadsheet

WPS Spreadsheet supports all standard and advanced text functions, including TRIM, RIGHT, and SUBSTITUTE, allowing you to manipulate and extract text seamlessly. It provides a lightweight, highly compatible environment for your data processing tasks.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the workbook containing the text strings you want to extract words from.
2. Select an empty cell: Click on a blank cell adjacent to your text data.
3. Input the text extraction formula: Type =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))) and press Enter to extract the last word.
4. Apply to multiple rows: Drag the fill handle located at the bottom right corner of the cell downwards to apply the formula to the rest of your column.

Fully compatible with Microsoft Excel formulas and .xlsx formats.Includes a comprehensive suite of text manipulation functions.Lightweight and fast, ideal for handling complex formulas on large datasets.Free to use with a familiar, easy-to-navigate tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

What if the text string has a trailing space at the end?

If there is a trailing space, the extraction formulas might return a blank space instead of the last word. To fix this, always wrap your cell reference in the TRIM function first, which removes any accidental leading or trailing spaces before processing the text.

Can I extract the first word instead of the last?

Yes. To extract the first word, you can use the LEFT and SEARCH functions. For example, the formula =LEFT(A1, SEARCH(" ", A1) - 1) will find the first space and return all characters before it.

Does WPS Spreadsheet support the newer TEXTSPLIT and TAKE functions?

WPS Spreadsheet continuously updates to include the latest formula functionalities. For the highest compatibility with classic Excel files across all versions, utilizing the SUBSTITUTE and RIGHT combination is highly recommended.

Excel & Data Problems