Quick, clean, and to the point

Remove last word

Excel formula: Remove last word
Generic formula 
=MID(A1,1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)

To remove the last word from a text string, you can use a formula based on the MID function, with help from SUBSTITUTE, LEN, and FIND. In the example shown, the formula in cell B5 is:

=MID(B5,1,FIND("~",SUBSTITUTE(B5," ","~",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))))-1)

This formula uses the MID function to remove the last word from a text string. The main challenge is to figure out where the last word begins.

The formula is a bit convoluted, but the steps are simple. We first count how many spaces exist in the text using LEN and SUBSTITUTE. Next, we use the somewhat obscure instance argument in the SUBSTITUTE function to replace the last space with a tilde (~). Finally, we use FIND to figure out where the tilde is, and the MID function to discard everything after the tilde.

Working from the inside-out, we use the LEN and SUBSTITUTE functions to count how many spaces appear in the next:

LEN(B5)-LEN(SUBSTITUTE(B5," ","")) // returns 6

For the text in B5, this returns 6. This page explains this part of the formula in more detail. This tells us we want to cut the text at the 6th space character.

Note: if words are separated by more than one space, you'll want to normalize spaces first with the TRIM function.

The number 6 is then plugged into another SUBSTITUTE as the "instance number":

SUBSTITUTE(B5," ","~",6) // insert tilde

Here, we are replacing the 6th instance of a space (" ") with a tilde (~). After SUBSTITUTE runs, it returns this string:

"It's been seven hours and fifteen~days"

Note: we use a tilde (~) only because it's a rarely occurring character. You can use any character you like, so long as it doesn't appear in the source text.

Next, we use the FIND function to locate the tilde:

FIND("~","It's been seven hours and fifteen~days")-1

FIND returns 34, since the tilde is the 34st character. We subtract 1 from this number, because we don't want to include the last space in the final result. We can now simplify the formula to:

=MID(B5,1,33) // extract final text

The MID function then returns characters 1-33:

"It's been seven hours and fifteen"

With a custom delimiter

The same formula can be used with a different delimiter. For example, to remove all text after the last forward slash "/", you can use:


Remove last n words

You can adapt the formula to remove the last 2 words, last 3 words, etc. The general form is:


where d is the delimiter, and n is the number of words to remove.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.