Extract last 2 (or N) words & remove the last word (TEXTAFTER / TEXTBEFORE -N)

Negative instance_num (-1, -2): right-to-left word slicing made effortless!

Extracting the last two words ("San Francisco" or "250 kg") or dropping a trailing unit/suffix ("Hydraulic Hose 25mm" -> "Hydraulic Hose") used to require cryptic RIGHT(A2, LEN(A2) - FIND("~", SUBSTITUTE(A2, " ", "~", LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))) formulas!

Passing a NEGATIVE 3rd [instance_num] argument to TEXTAFTER and TEXTBEFORE counts spaces backward from the end of TRIM(A2): TEXTAFTER(TRIM(A2), " ", -2) grabs the last 2 words; TEXTAFTER(TRIM(A2), " ", -1) grabs the final word; and TEXTBEFORE(TRIM(A2), " ", -1) drops the final word while keeping everything before it! Wrap IFERROR(..., TRIM(A2)) so 1-word strings never throw #N/A.

Extract the last B2 words from any text string (single-word safe)

Enter a positive whole-number count. If fewer than N space-delimited tokens exist, return the complete trimmed input. Ordinary spaces delimit tokens; tabs, nonbreaking spaces and language-specific word boundaries are not handled here. A blank input returns blank.

=IF(OR(NOT(ISNUMBER(B2)),B2<1,B2<>INT(B2)),"Invalid word count",IF(COUNTBLANK(A2)>0,"",IFERROR(TEXTAFTER(TRIM(A2)," ",-B2),TRIM(A2))))

Remove the last word (or trailing unit code) with TEXTBEFORE(..., -1)

This removes the last token regardless of its meaning. It does not recognize company suffixes or units: confirm that the final token is disposable before applying it to real data. Single-token input becomes empty.

=IFERROR(TEXTBEFORE(TRIM(A2), " ", -1), "")

Further reading

• Microsoft Support — TEXTAFTER function
• Microsoft Support — TEXTBEFORE function