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

Sometimes you need to remove the last word, or several words, at the end of text in Excel cells: trim a color or size from product names, cut a city off the end of search queries, or clean up a trailing note in descriptions.

Excel has no single function for this, but you can combine several functions to search from right to left in a cell. Besides regular formulas, there are other ways too, and I’ll go through them in order. This page is part of the delete words in Excel section.

Short answer for those in a hurry: the fastest way is to select the cells and remove the last word right in place with !SEMTools, no formulas needed. If you need a formula, in Excel 365 it’s =IFERROR(TEXTBEFORE(A1," ",-1),""). In older versions, or if you want to understand how it works, read on.

When you need to remove the last word in Excel

A few typical situations:

• Product names: “Nike Air Max 90 white” needs to become “Nike Air Max 90” to group all colors of one model.
• Search queries and keywords: “hotels in Boston” → “hotels in”, when you need the query stem without the location.
• Names and addresses: removing a suffix or a ZIP code at the end of the line.
• Imported data: a status, a date or a code glued to the end of the text after an export.

Remove the last word using a formula

Unlike removing the first word from a cell, where the formula is quite simple because the SEARCH function searches from left to right, the formula for removing the last word is much more complex.

Why? Because there is no version of SEARCH that would look for a character from right to left. Below is a step-by-step breakdown of a formula that finds the last space with a combination of functions anyway. After that, removing the last word is easy.

1. Measure the length of the string with the LEN function.

=LEN(A1)

2. Replace the spaces in the string with nothing. The SUBSTITUTE function will help here.

=SUBSTITUTE(A1," ","")

3. Measure the length of the string without spaces. To get this formula, replace A1 in step 1 with the expression from step 2 (without the equal sign).

=LEN(SUBSTITUTE(A1," ",""))

4. Subtract the second length from the first. The result is the number of spaces in the original string.

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))

5. Pick a character that is guaranteed not to be in your data. If you are sure, you can use a specific character, for example “\”. To be on the safe side, use the UNICHAR function: it returns any character from the Unicode table. For example:

=UNICHAR(23456)

It returns “宠”. I believe it’s Chinese or Japanese, and it’s unlikely to appear in an English product list :)

6. Replace the last space with this character. SUBSTITUTE has an optional fourth argument: the number of the occurrence to replace. In step 2, we didn’t use it, so all spaces were removed. Now we put the formula from step 4 into it, and the formula from step 5 becomes the third argument.

=SUBSTITUTE(A1," ",UNICHAR(23456),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))

7. Find the position of this character. This time SEARCH does the job perfectly: the character occurs only once, so it doesn’t matter which direction we search in.

=SEARCH(UNICHAR(23456),SUBSTITUTE(A1," ",UNICHAR(23456),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))

8. Subtract 1 from the result to get the position of the character before it.

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

9. Take the text up to this position from the original cell with the LEFT function.

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

10. Replace A1 with " "&A1 everywhere in the formula. Why? If a cell contains a single word, there are no spaces in it, the occurrence number becomes 0, and SUBSTITUTE returns #VALUE!. An extra space at the beginning guarantees at least one space.

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

11. Wrap the result in the TRIM function. Step 10 protects the formula from errors but adds an extra space on the left. TRIM removes it, along with any other extra spaces between words or at the end of the string.

So, here is the formula for removing the last word from a cell:

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

This formula takes the text from cell A1 and cuts it off at the last space. If the cell contains a single word, the result is an empty string.

Remove the last N words with a formula

The same formula removes several words from the end if you replace not the last space, but the space N positions from the end. Put the number of words to remove in cell B1:

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

With B1 = 1, it works exactly like the formula above. If B1 is greater than the number of words in the cell, IFERROR returns an empty string.

Remove the last word in Excel 365 with TEXTBEFORE

In Excel 365, the task is solved by the TEXTBEFORE function. Its third argument is the occurrence number of the delimiter, and a negative number counts from the end of the text. So −1 means the last space:

=IFERROR(TEXTBEFORE(A1," ",-1),"")

To remove the last N words, use −N, for example the last 2 words:

=IFERROR(TEXTBEFORE(A1," ",-2),"")

TEXTBEFORE returns #N/A when the cell has fewer spaces than requested, which is why it’s wrapped in IFERROR. If you prefer to keep single-word cells as they are, replace the last argument "" with A1.

Remove the last word with a REGEX formula

Regular expressions in Excel give anyone who knows their syntax almost unlimited options for finding, extracting, replacing and deleting text, without monstrous formulas like the one above.

In Excel 365, the REGEXREPLACE function is built in. In Excel 2010–2021, the function with the same name and syntax is available right after you install the !SEMTools add-in, even in the free version. Here is how elegant the formula for removing the last word looks:

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

Quick breakdown of the regex:

• \s — a space (or another whitespace character) before the last word;
• \S+ — one or more non-space characters, which is the word itself;
• $ — the end of the text, so only the last word matches.

To remove the last 2 words, repeat the group: =REGEXREPLACE(A1,"(\s\S+){2}$",""). Note one difference from the formulas above: if a cell contains a single word, the regex leaves it unchanged, because there is no space before it.

Before and after: examples

Here is how the formulas work on real data:

Remove the last word(s) in a whole column without formulas

What if you don’t want to create a helper column and need to change the data right in place? This is where !SEMTools helps. In the “Delete words” menu of the DELETE group, you can remove the last word, the last 2 or 3 words, or any number of words from the end of each cell. Select the range and click the button:

The same goes for any number of words: use the “Last N” option to specify how many words to remove, counting from the right:

Why the last word isn’t removed: common mistakes

• Nothing seems to be removed. The cell most likely ends with a space: “red shoes ” → the formula removes the empty “word” after it. Replace A1 with TRIM(A1) in the formula or remove extra spaces first.
• The formula returns #NAME?. TEXTBEFORE exists only in Excel 365 and Excel 2024, and REGEXREPLACE before Excel 365 works only where !SEMTools is installed. Use the classic formula in older versions.
• Single-word cells became empty. This is expected: the only word is also the last one. If you need to keep such cells, use IFERROR(TEXTBEFORE(A1," ",-1),A1) or the regex version.
• The source cells didn’t change. A formula always returns the result in another cell. To keep only the result, replace the formulas with values and delete the source column.

Summary: which way to remove the last word

I hope you found what you were looking for on this page and were able to remove the last words from your cells. If you need a formula, TEXTBEFORE is the shortest one in Excel 365, the classic formula with SUBSTITUTE works in any version, and the regex is the most flexible.

Still, any formula means remembering the syntax, typing it in, filling it down, pasting values and deleting the helper column. With !SEMTools, you select the column, choose how many words to remove from the end, and the cells change right in place in a couple of clicks.

You may also be interested in related operations: remove the last N characters, extract the first words from cells or remove a list of words from text.

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
• Remove first N characters (from left) in Excel cells