Extract n words from string

I'm using the following formula to extract the first five words from a string in cell A1.

=TRIM(LEFT(A1,FIND("^",SUBSTITUTE(TRIM(A1)&" "," ","^",5))-1))

It works great except when the string has fewer than 5 words in it, in which case it returns a "Value#" error.

Any suggestions on how to edit this so it can handle strings that are only 1, 2, 3, or 4 words long?

Originally Posted by daddylonglegs

This small change should do it

=TRIM(LEFT(A1,FIND("^",SUBSTITUTE(TRIM(A1)&REPT(" ",5)," ","^",5))-1))

That works great! On a related note, I'm now trying to pick up where that formula leaves off, and extract the *next* five words from the string, and so forth.

So if the string in A1 is "The quick brown fox jumps over the lazy dog." then I want

B1 = The quick brown fox jumps
C1 = over the lazy dog.

But perhaps there's a better approach. The reason for all this is I have a text box (Excel 2007, so Insert > Text Box) that gets its value from a formula (=A1). But as I understand it there's no decent way to enable word wrap in a text box that gets its values from a formula. My text box is only wide enough to show about 5 words at a time, so I was going to break the original string (which may vary from 1 to 20 words) into at most four 5-word lines, and use four text boxes (=B1, =C2, =D2, =E2) to show the result. I have to assume there's a better way but I've searched to no avail.

Strange. I just recreated the text boxes and the words now wrap just fine.

Thanks anyway for the help.