Find the First or Last So Many Words in a Text String

[Rick Rothstein]

Using from the formula I posted in my "Get Field from Delimited Text String" article and the formula Haseeb Avarakkan posted in response to my "Get "Reversed" Field from Delimited Text String" article as a base, here are formulas for finding the first N words and the last N words in a text string.

First N Words: =TRIM(LEFT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),N*99))

Last N Words: =TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),N*99))

where N is the number of words you want to retrieve. The only caution is that the text in A1 must be less than 100 characters (that is what the 99 establishes). If your text is longer, then you would need to change the two 99's in each formula to a number equal to, or larger than, the maximum number of characters the text in A1 can have.