Remove Last Word? How to Do It in Excel, Google Sheets, and More

This guide answers the question directly: yes, you can remove the last word from a string in Excel, Google Sheets, and most programming languages, and here are the exact formulas and methods to do it. You’ll learn the core formula logic, see worked examples, and get step-by-step instructions for the most common tools.

Formula

The general formula to remove the last n words from a text string is:

LEFT(text, LEN(text) - FIND(SUBSTITUTE(text, " ", "@", LEN(text) - LEN(SUBSTITUTE(text, " ", "")) + 1 - n)))

Where:

• text – the original string (e.g., “Hello world example”).
• n – the number of words to remove from the end (e.g., 1 removes the last word).
• LEN(text) – total number of characters in the text.
• SUBSTITUTE(text, ” “, “”) – removes all spaces, so LEN(text) - LEN(SUBSTITUTE(text, " ", "")) gives the number of spaces, which equals the number of words minus 1.
• FIND(SUBSTITUTE(…)) – finds the position of the space that starts the last word (or the word before it if removing more than one).

This works in Excel and Google Sheets. In programming languages like Python or JavaScript, you would use string splitting and joining instead.

Worked Example

Let’s use the default text “Hello world example” and remove 1 word (n=1).

1. Original text: “Hello world example” (length = 19 characters).
2. Remove spaces: “Helloworldexample” (length = 17 characters).
3. Number of spaces = 19 – 17 = 2 (so there are 3 words).
4. We want to remove 1 word, so we need the position of the space before the last word. That is space number 2 (since 2 + 1 – 1 = 2).
5. Substitute the 2nd space with a placeholder: “Hello world@example”.
6. FIND(“@”) returns 12 (the position of the space between “world” and “example”).
7. Result = LEFT(“Hello world example”, 19 – 12) = LEFT(…, 7) = “Hello world”.

This matches the calculator output.

Why You Might Need to Remove the Last Word

Removing the last word is a common data-cleaning task. You might have a list of full names and need just the first and middle names, or a list of product codes where the last segment is a category. It’s also used in text preprocessing for natural language processing, or when you need to truncate a string in a specific way without cutting off a word mid-way.

How to Remove the Last Word in Excel

In Excel, you can use the formula above directly. For example, if your text is in cell A1:

1. Click on cell B1.
2. Enter the formula: =LEFT(A1, LEN(A1) - FIND(SUBSTITUTE(A1, " ", "@", LEN(A1) - LEN(SUBSTITUTE(A1, " ", "")) + 1 - 1)))
3. Press Enter. The result will appear in B1.
4. Drag the fill handle down to apply to other rows.

If you want to remove more than one word, change the last number in the formula (e.g., use 2 to remove the last two words).

How to Remove the Last Word in Google Sheets

Google Sheets uses the exact same formula syntax. The only difference is that you might need to use semicolons instead of commas if your regional settings require it (e.g., in some European locales). Just type the formula in a cell and it will work.

Alternatively, you can use a simpler approach with SPLIT and JOIN in Google Sheets:

• =JOIN(" ", QUERY(TRANSPOSE(SPLIT(A1, " ")), "select * offset 0 limit " & COUNTA(SPLIT(A1, " ")) - 1))
• This splits the text into words, uses QUERY to limit the number of rows, and then joins them back with spaces.

However, the standard formula is more portable and works in both Excel and Sheets.

How to Remove the Last Word in Python, JavaScript, and Other Languages

In programming, the logic is simpler because you can split the string into a list of words and then join the first n-1 words back together.

Python:

text = "Hello world example"
words = text.split()
result = " ".join(words[:-1])  # removes the last word
print(result)  # Output: Hello world

JavaScript:

let text = "Hello world example";
let words = text.split(" ");
words.pop();  // removes the last element
let result = words.join(" ");
console.log(result);  // Output: Hello world

SQL (SQL Server):

SELECT LEFT(column, LEN(column) - CHARINDEX(' ', REVERSE(column))) FROM table;

This works by reversing the string, finding the first space (which is the last space in the original), and then taking the left part.

Common Mistakes and Pitfalls

• Trailing spaces: If your text has a trailing space, the formula will not work correctly because it will count the trailing space as a separator. Use TRIM() first: =TRIM(A1).
• Multiple spaces between words: The formula assumes single spaces. If you have double spaces, it will treat them as separate words. Use TRIM() and SUBSTITUTE to normalize spaces.
• Empty text or single word: If the text has only one word, the formula will return an error. You can wrap it with IFERROR to handle that case.
• Punctuation: The formula treats punctuation as part of the word. If you want to remove the last word including a period, you might need to adjust the separator.

Alternatives to Removing the Last Word

Sometimes you don’t need to remove the last word but instead want to extract it. To get the last word, you can use:

• Excel: =TRIM(RIGHT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100))
• Google Sheets: =INDEX(SPLIT(A1, " "), COUNTA(SPLIT(A1, " ")))
• Python: text.split()[-1]

If you need to remove the first word instead, use similar logic but target the first space.

Frequently Asked Questions

How do I remove the last word in Excel if there are multiple spaces?

First, normalize the text with TRIM() to remove extra spaces, then use the standard formula. For example: =LEFT(TRIM(A1), LEN(TRIM(A1)) - FIND(SUBSTITUTE(TRIM(A1), " ", "@", LEN(TRIM(A1)) - LEN(SUBSTITUTE(TRIM(A1), " ", "")) + 1 - 1))).

Can I remove the last word in Google Sheets without a formula?

Yes, you can use the “Split text to columns� feature to split by space, then delete the last column, and then use JOIN or concatenate. However, formulas are more dynamic and update automatically.

What if my text is in a different language with different word separators?

The formula works with any separator as long as you replace the space with the correct delimiter. For example, if words are separated by commas, replace ” ” with “,” in the formula.

How do I remove the last word in Notepad++ or other text editors?

You can use regular expressions. In Notepad++, press Ctrl+H, select “Regular expression�, and replace \s+\S+$ with nothing. This matches the last word and the space before it.

Why does my formula return #VALUE! in Excel?

This usually happens when the text is empty or contains only one word. Use IFERROR to handle these cases, or check that your cell references are correct.

Is there a way to remove the last word in a cell without using a formula?

You can use Excel’s “Flash Fill� (Ctrl+E) after typing the desired result in a neighboring cell, or use Power Query to split and recombine. These are manual but sometimes faster for one-off tasks.

Related

• Install Node.js with NVM on a VPS
• Flashforge Dreamer SD Card Format: FAT32 Guide
• Flash Drive as a Portable Web Server: How-To

in text | Text | - | "Hello world example" | 1 | 500
in n | Words to remove | - | 1 | 0 | 10
out result | Result | - | LEFT(text, LEN(text) - FIND(SUBSTITUTE(text, " ", "@", LEN(text) - LEN(SUBSTITUTE(text, " ", "")) + 1 - n))) | 0