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: 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”: Here, we are replacing the 6th instance of a space (" “) with a tilde (~). After SUBSTITUTE runs, it returns this string: 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 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: The MID function then returns characters 1-33:
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
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
