Remove last word
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:
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.