Delete the last word in Excel cells / delete the last N words

Remove the Last Word in Excel

Sometimes when working in Excel, you need to remove the last word or several words at the end of text in cells. This can be useful for cleaning up product names, trimming extra text from descriptions, or adjusting keyword lists for ads.

We’ll look at two approaches — using Excel formulas and using !SEMTools to do it instantly without formulas.

Remove the Last Word Using a Formula

Unlike removing the first word (which is straightforward because Excel’s SEARCH function works left-to-right), removing the last word is more complex since Excel doesn’t have a built-in function to search from right to left.

Here’s the step-by-step idea:

1. Find the length of the original text with LEN.
2. Find the length of the same text without spaces (using SUBSTITUTE).
3. Subtract the two lengths to get the number of spaces.
4. Replace the last space with a unique character that doesn’t exist in your text.
5. Find the position of that character with SEARCH.
6. Extract everything before it with LEFT.
7. Wrap the result with TRIM to clean up any extra spaces.

Final formula for removing the last word from cell A1:

=TRIM(LEFT(" "&A1,SEARCH(UNICHAR(23456),SUBSTITUTE(" "&A1," ",UNICHAR(23456),LEN(" "&A1)-LEN(SUBSTITUTE(" "&A1," ",""))))-1))

This approach works for text with multiple words and won’t break if there’s only one word.

Remove the Last Word with a Simple REGEX Formula

If you have !SEMTools installed, you can skip the long formula and use REGEXREPLACE instead:

=REGEXREPLACE(A1,"\s\S+$","")

This removes the last space and all characters after it, leaving the rest of the text intact.

Remove the Last Word(s) in a Whole Column Without Formulas

If you want to update data in place without creating extra columns, !SEMTools can do this in one click. You can choose to remove just the last word, the last 2 words, or any number of words from the right side of each cell.

Example — removing the last 2 words in all cells of a column:

Or specify your own number of words to remove with the “Last N” option:

Conclusion

Now you know several ways to remove the last word or several words from Excel cells. If you want to do it without building complicated formulas, !SEMTools gives you the fastest option.

See also:

• Remove the first word or first N words in Excel
• Remove the last N characters from cells
• Add a symbol, word, or text to all cells in a column
• Remove a list of words from text

This post is also available in RU.