Retrieve first three words in a cell

[Richww2]

I need to retrieve the first three words in a cell. Also, if there are less than three words in a cell I need it to retrieve the two or the one word still.

Ex.
A1 = This is a test
A2 = Still a test
A3 = Just test
A4 = Test
A5 = J P Morgan Chase

I want it to return

B1 = This is a
B2 = Still a test
B3 = Just test
B4 = Test
B5 = J P Morgan

Please help me! I've been trying to use Trims and Left Substitutes but I haven't had any luck figuring it out. Every time I get something that gets me three words, it won't work for one or two words. Thank you so much in advance!

Also, to save time so I'm not on here later asking about it. Is there one for the first two and four words with the same criteria as above?

[snb]

=if(iserror(search("#",substitute(a8," ","#",3))),a8,left(substitute(a8," ","#",3),search("#",substitute(a8," ";"#",3))-1))  

[MaczaQ]

try this

Please Login or Register  to view this content.

copy into module and then use in your worksheet in any cell
example:
formula in cell B1: =splitit(A1)

Best Regards

[Richww2]

Thanks a ton both of you. They worked great! I can finally stop pulling my hair out.

[snb]

or this UDF:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.