Remove last word

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:

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

Remove last n words

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

=MID(A1,1,FIND("~",SUBSTITUTE(A1,d,"~",LEN(A1)-LEN(SUBSTITUTE(A1,d,""))-(n-1)))-1)

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