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)

How this formula works

This formula uses the MID function to remove the last word from a text string. It does this by extracting text starting at character 1 and ending before the last word. Almost all of the work is in finding out where to stop. In other words, the challenge is to figure out where to stop.

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 formula is explained in more detail here.

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

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

Here, we are replace 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 another 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.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables